## How to Sum in Vertical Range

If we want to add numbers based on some conditions in Excel worksheet, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method about applying SUMIFS function to sum numbers with different vertical ranges.

We will introduce you the syntax, arguments of SUMIFS function, and let you know how this formula works to reach your goal. After reading the article, you may have a simple understanding of SUMIFS function and know how can we use this function to sum numbers with vertical range.

## 1. Sum in Vertical Range Example

Refer to the upper side table, it lists the amount for ‘Apple Pie’, ‘Hamburger’ and ‘Salad’ on weekdays (from ‘Monday’ to ‘Friday’) in two weeks. Start from column C, it lists the amount for the three kinds of food on each weekday for two weeks in vertical direction. In the bottom side table, it records the total amount for the three kinds of food in two weeks.

Now, we need to create a formula that can return the total amount for each food in two weeks based on different weekdays. This question can be seen as we need to sum data from a vertical range with specific criteria. For example, in this instance, in C9, we want to enter a formula that can return the total amount for ‘Apple Pie’ on ‘Monday’ in ‘two weeks’ (week1 and week2). Besides, when dragging the formula to other cells (in range reference C9 to G11), we need the formula can adjust cell reference and range reference automatically and return the correct result based on updated parameters properly.

In this instance, we need to pick up numbers with criteria from each vertical range, then sum them together. To fix this issue by formula, we can apply excel built-in function SUMIFS here.

## 2. Sum in Vertical Range Formula – SUMIFS FUNCTIONS

Step1: In C9, enter the formula

``=SUMIFS(C\$2:C\$7,\$B\$2:\$B\$7,\$B9)``

Step2: Press Enter after typing the formula.

We can see in cell C9 result 9 is returned, it is equal to 5 from C2 plus 4 from C4, the formula works correctly.

Step3: Drag the ‘Auto Fill’ handle to make range reference C9:G11 filled with the formula. Then we can see that results are automatically calculated and displayed in each cell correctly.

## 3. SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

Syntax:

``=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)``

[] part can be omitted.

For SUMIFS function, it supports wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

ALL ARGUMENTS

The formula is =SUMIFS(C\$2:C\$7,\$B\$2:\$B\$7,\$B9). Refer to above SUMIFS function introduction, we will split it to several parts.

### SUMIFS – SUM RANGE

C\$2:C\$7 is the ‘sum range’ in this case.

You can see we add “\$” before row index 2 and 7, that’s because when dragging the formula from C9 to C10 (formula is moved in vertical direction), cell reference will be moved in vertical direction by default, so we need add “\$” before row index to lock the range in vertical direction, make sure the sum range can only be adjusted in vertical, but in horizontal, it is absolutely fixed.

See screenshot below, if “\$” is omitted, formula is updated to =SUMIFS(C3:C8,\$B\$2:\$B\$7,\$B10) in C10, sum range is moved from range reference C2:C7 to C3:C8 improperly, the returned result is 9 incorrectly.

Select C\$2:C\$7 from formula in the formula bar, the press F9, values in this range are expanded in an array.

### SUMIFS – CRITERIA RANGE

\$B\$2:\$B\$7 is the criteria range. We have only one criteria range in this instance.

Criteria range contains three kinds of food, due to there are two weeks, so we list them twice.

You can see we add “\$” before column and row index both, that’s because range \$B\$2:\$B\$7 is an absolute range. No matter copy formula to which cell reference, the criteria range is still \$B\$2:\$B\$7 and will not be changed. So we add ‘\$’ to lock this range.

Select \$B\$2:\$B\$7 from formula in the formula bar, the press F9, values in this range are expanded in an array.

### SUMIFS – CRITERIA

\$B9 is the criteria. \$B9=”Apple Pie”.

As we want to calculate total amount for the provided food, so can set criteria as B9 (B9=”Apple Pie”), B10 (B10=”Hamburger”), and B11 (B11=”Salad”). As column B is fixed, so we add ‘\$’ before column B.

We can also enter food name directly, as SUMIFS function supports texts, but they should be enclosed into double quotes “”, so for criteria “Apple Pie”, we can directly enter “Apple Pie” in criteria argument.

Select \$B9 from formula in the formula bar, the press F9, criteria value is displayed.

## 4. Sum in Vertical Range – Formula Explantation

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference or cell reference, in the formula bar, the formula is displayed as:

``=SUMIFS({5;10;5;4;12;4},{"Apple Pie";"Hamburger";"Salad";"Apple Pie";"Hamburger";"Salad"},"Apple Pie")``

There is one pair of criteria range and criteria.

{“Apple Pie”} – Criteria

If values from the criteria range are matched with the criteria “Apple Pie”, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new array:

{True;False;False;True;False;False} – after comparing with criteria “Apple Pie”

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above array is converted to below array which consists of numbers “1” and “0”.

{1;0;0;1;0;0} – for criteria “Apple Pie”

Now, we have below two pairs of arrays:

{5;10;5;4;12;4} – sum range

{1;0;0;1;0;0} – for criteria “Apple Pie”

Multiply the two elements in the same position in the two arrays. Then we can get a new array.

{5;0;0;4;0;0}

Add all products in above array, we get 9 finally. Actually, in step#2, SUMIFS function returns 9 after calculation. For the other cells, the workflow is similar.

1. In this case, as we have only one pair of criteria range and criteria, we can also apply SUMIF function as well. The difference is SUMIF function only supports one group of criteria range and criteria, and its sum range is listed at the end.

Enter =SUMIF(\$B\$2:\$B\$7,\$B9,C\$2:C\$7), we can get the same result as applying SUMIFS.

## 5. Video: How to Sum in Vertical Range in Excel

In this video, you will learn how to use the SUMIF formula to sum values in a vertical range in Excel.

## 6. Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

## How to Sum Values Based on Month and Year in Excel

We often do some summary or statistic at the end of one month or one year. In these summary tables, there are at least two columns, one column records the date, and the other column records the sales or product number or total costs etc. If we want to calculate the total values based on a certain month of a year, for example if we want to calculate the total sales for January 2020, how can we do? This article will help you to solve this problem.

See the example below. A column lists some dates. B column lists the sales. E column lists the month and year we want to calculate the total sales for. F column is used for showing the returned value by formula.

## 1. Sum Values Based on Month and Year by SUMIF Function

Step 1: In cell F2, enter the formula:

`` =SUMIFS(B2:B16,A2:A16,">="&DATE(2019,1,1),A2:A16,"<="&DATE(2019,1,31))``

In SUMIFS function, B2:B16 is the sum range, A2:A16 is the criteria range. “>=”&DATE(2019,1,1) and “<=”&DATE(2019,1,31) are the two criteria. Details please see below screenshot.

Verify that total sales 1500 is displayed for January 2019 after calculation.

Step 2: If we want to calculate total sales based on a certain period, we can change the parameters in DATE function. For example, to calculate the total sales for period 1/3/2019 – 1/3/2020, we can enter the formula:

`` =SUMIFS(B2:B16,A2:A16,">="&DATE(2019,1,3),A2:A16,"<="&DATE(2020,1,3))``

Above all, you can change the date in DATE function to do sum per your demands.

## 2. Sum Values Based on Month and Year by SUMPRODUCT

Step 1: In cell F2, enter the formula

``=SUMPRODUCT((MONTH(A2:A16)=1)*(YEAR(A2:A16)=2019)*(B2:B16))``

In this formula we also use MONTH function and YEAR function to filter date from range A2:A16 based on criteria ‘Date is included in period January 2019’. B2:B16 is the sum range.

Step 2: Click Enter to get the result. Verify that we get the same result in method #1 step#1.

## 3. Sum Values Based on Month and Year with User Defined Function (VBA Code)

If you want to sum values based on Month and Year with User Defined Function in Excel, you can use these steps:

Step1: Open the Visual Basic Editor by pressing Alt + F11.

Step2: In the Visual Basic Editor, click on “Insert” from the menu bar and select “Module” to create a new module.

Step3: In the new module, enter the following code, save the module with a suitable name.

```Function SumValuesByMonthAndYear(sumRange As Range, dateRange As Range, sumMonth As Integer, sumYear As Integer) As Double
Dim i As Integer
Dim sum As Double

For i = 1 To sumRange.Rows.Count
If Month(dateRange.Cells(i, 1).Value) = sumMonth And Year(dateRange.Cells(i, 1).Value) = sumYear Then
sum = sum + sumRange.Cells(i, 1).Value
End If
Next i

SumValuesByMonthAndYear = sum

End Function
```

Step4: go back to your Excel worksheet and enter the following formula without the quotes into a blank cell.

``=SumValuesByMonthAndYear(B2:B16, A2:A16, 1, 2019)``

This function takes four arguments:

• sumRange – The range of cells containing the values to sum
• dateRange – The range of cells containing the dates to check for the month and year
• sumMonth – The month to sum for (as an integer from 1 to 12)
• sumYear – The year to sum for (as a four-digit integer)

The function then loops through the dateRange, checks if each date matches the sumMonth and sumYear, and adds the corresponding value from the sumRange to the sum variable.

Finally, the function returns the sum variable as the result.

## 4. Video: Sum Values Based on Month and Year

This video will demonstrate how to Sum Values Based on Month and Year in Excel using the Formula and VBA Code.

## 5. Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel MONTH Function
The Excel MONTH function returns the month of a date represented by a serial number. And the month is an integer number from 1 to 12.The syntax of the MONTH function is as below:=MONTH (serial_number)…
• Excel DATE function
The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
• Excel YEAR function
The Excel YEAR function returns a four-digit year from a given date value, the year is returned as an integer ranging from 1900 to 9999. The syntax of the YEAR function is as below:=YEAR (serial_number)…

## How to Sum in Horizontal Range

To add numbers together we need to apply SUM function. But if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. In this article, we will introduce you the method of applying SUMIFS function about sum numbers from different horizontal ranges.

We will introduce you the syntax, arguments of SUMIFS function, and let you know how this formula works to reach your goal. After reading the article, you may have a simple understanding of SUMIFS function and know how can we use this function to sum numbers with horizontal range.

## 1. Sum in Horizontal Range using Formula

### a. Example Introduction

Refer to the left-hand side table, it lists the amount for ‘Apple Pie’, ‘Hamburger’ and ‘Salad’ on weekdays (from ‘Monday’ to ‘Friday’) in two weeks. Start from row 3, in horizontal, it lists the amount for the three kinds of food on each weekday in two weeks. In the right-hand side table, it records the total amount for the three kinds of food in two weeks.

Now, we need to create a formula that can return the total amount for each food in two weeks based on different weekdays. This question can be seen as we need to sum data from a horizontal range with specific criteria.

For example, in this instance, in I3, we want to enter a formula that can return the total amount for ‘Apple Pie’ on ‘Monday’ in ‘two weeks’ (week1 and week2). Besides, when dragging the formula to other cells (in range reference I3 to K7), we need the formula can adjust cell reference and range reference automatically and return the correct result based on updated parameters properly.

In this instance, we need to pick up numbers with criteria from each horizontal range, then sum them together. To fix this issue by formula, we can apply excel built-in function SUMIFS here.

### b. How to Use SUMIFS FORMULA

Step1: In I3, enter the formula =SUMIFS(\$A3:\$G3,\$A\$2:\$G\$2,I\$2).

Step2: Press Enter after typing the formula.

We can see in cell I3 result 9 is returned, it is equal to 5 from B3 plus 4 from E3, the formula works correctly.

Step3: Drag the ‘Auto Fill’ handle to make range reference I3 to K7 filled with the formula. Then we can see that results are automatically calculated and displayed in each cell correctly.

### c. How The SumIFS Formula Works

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference or cell reference, in the formula bar, the formula is displayed as:

``=SUMIFS({5,10,5,4,12,4},{"Apple Pie","Hamburger","Salad","Apple Pie","Hamburger","Salad"},"Apple Pie")``

There is one pair of criteria range and criteria.

``{"Apple Pie","Hamburger","Salad","Apple Pie","Hamburger","Salad"} ``
``{"Apple Pie”}``

If values from the criteria range are matched with the criteria “Apple Pie”, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new array:

``{True,False,False,True,False,False} ``

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above array is converted to below array which consists of numbers “1” and “0”.

For criteria “Apple Pie”:

``{1,0,0,1,0,0} ``

Now, we have below two pairs of arrays:

sum range:

``{5,10,5,4,12,4} ``

For criteria “Apple Pie”:

``{1,0,0,1,0,0} ``

Elements are vertical-aligned in the two arrays. Multiply the two elements in the same position in the array. Then we can get a new array.

``{5,0,0,4,0,0} ``

Add all products in above array, we get 9. Actually, in step#2, SUMIFS function returns 9 after calculation. For the other cells, the workflow is similar.

Enter =SUMIF(\$B\$2:\$G\$2,I\$2,\$B3:\$G3), we can get the same result as applying SUMIFS.

## 2. Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

## If Cell Contain Specific Text

This article will explain how to check if a cell contains text or a specific text string in Google Sheets or Microsoft Excel spreadsheets. In our daily work, we often look for specific text in worksheets containing large amounts of data or need to determine if a specific cell area contains text values.

We may also encounter the need to find out if a cell contains partial text or we need to find a specific text string in a cell by using VBA code.

## Cell Contains Specific Text in Excel & Google Sheets

Below we will show how to check if a cell contains a specific text by using a formula in Excel or Google Sheets.

If you want to check if a given cell contains a specific text, then you can use the SEARCH function in combination with the ISNUMBER function to create a nested formula. You can refer to this general formula below.

`=ISNUMBER(SEARCH(specific_text,text))`

In the general formula above, specific_text is the specific text you want to find, and text is the text string or cell reference you want to find that contains the specific text.

If you found a specific text, then the formula will return TRUE, otherwise it will return FALSE.

If you need to find partial text from the cell, then the SEARCH function is very suitable; by default, the SEARCH function will automatically find partial text to match.

For the example in this article, you can use the following formula to find specific text.

`=ISNUMBER(SEARCH(B4,A4))`

From the screenshot above, we can see that the formula supports partial text matching, where specific_text is “rest” and the text is “interest dividends“, and the result returned is TRUE, which means that partial text matching for a specific character is successful.

Let’s see how this formula works:

When the SEARCH function finds a specific text, it returns the position of that text in the searched text cell, and if not found, it returns #VALUE! . So, we can take advantage of this feature by simply using the ISNUMBER function to check if the SEARCH function returns a number, and if it is a number, it means that the specific text was found in the cell.

For the ISNUMBER function, it returns TRUE if the given argument is a number, otherwise it returns FALSE. therefore, when the SEARCH function can return the position of a specific character, then the ISNUMBER function returns TRUE. if the SEARCH function returns a #VALUE error, then the ISNUMBER function will return FALSE.

## Cell Contains Specific Text with Case Sensitive in Excel & Google Sheets

You can use the SEARCH function to find specific text and partial text matches, but the function is not case-sensitive.

If you want to check for specific text in a case-sensitive manner in a text cell, then you cannot use the SEARCH function, and you need to replace the SEARCH function with the FIND function, using the following formula.

`=ISNUMBER(FIND(B4,A4))`

As you can see from the above figure, specific_text “Rest” can no longer be successfully matched in text cell A4.

## If Cell Contains Specific Text Then in Excel & Google Sheets

If you want to do something else when you find a cell contains a specific text, for example, we do not want to return TRUE or FALSE results, but rather return some more meaningful text or results.

You can build an IF nested formula by nesting the above formula into the IF function as follows.

`=IF(ISNUMBER(SEARCH(B4,A4)),"Found", "No Found")`

Note: Because this formula uses the SEARCH formula, the result is case-insensitive.

## Sum If Cell Contains Text in Google sheets & Excel

If a column of cells contains a specific text string, you want to add the corresponding cell value of another column, then you can use the SUMIFS function contains a specific text of the cells corresponding to the data of another column to sum. The general formula is as follows.

=SUMIFS(sum_range, criteria_range,specific_text)

If you want to find all the cells in the cell region A4:A12 contains the “expen” string, and the corresponding value of another column B4:B12 to sum, then you can use the following formula.

`=SUMIFS(B4:B12,A4:A12, "*expen*")`

If you are using google sheets, then you can also accomplish this task by using the following formula, which is as follows.

`=SUM(filter(B4:B12, regexmatch(A4:A12,"expen")))`

As you can see from the chart above, the formula consisting of the SUM function and the FILTER function returns the same result as the EXCEL formula.

Note: Excel does not have a special wildcard formula that uses the “*” meta-character to match any character. There is a special regular expression function regexmatch in google sheet.

## Conditional Formatting If Cell Contains in Google Sheets & Excel

In Microsoft Excel, if you want to highlight a text cell containing the “expense” string, then you can refer to the following steps.

STEP 1: Select the cell range A4:A12, then in the HOME tab, click the Conditional Formatting button, and then click the New Rule submenu.

STEP2: The New Formatting Rule window will pop up, click on “Use a formula to determine which cells to format“, then enter the following formula in the text box.

`=SEARCH(“expense”,A4)`

STEP3: Click the Format button and select a color to fill the cells that can be matched successfully. Click OK.

STEP 4: You will see that all cells that contain the expense string are automatically highlighted.

In google sheets, if you want to highlight a text cell that contains an “expense” string, then you can refer to the following steps.

STEP1: Select the cell range that contains a specific text to be highlighted by conditional formatting

STEP 2: Click Format menu, then click Conditional Formatting submenu, the “Conditional format rules” window will pop up on the right side of the page.

STEP 3: Select Text Contains in the Format rules drop-down list and enter the specific text string “expense” in the text box

STEP 4: Select a highlight color in Formatting style and click the Done button.

STEP 5: Let’s see the final result.

## Related Functions

•  Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
• Excel SEARCH function
The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH  (find_text, within_text,[start_num])…
• Excel Find function
The Excel FIND function returns the position of the first text string (substring) from the first character of the second text string.The FIND function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the FIND function is as below:= FIND  (find_text, within_text,[start_num])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel Filter function
The FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…

## Average Of Numbers With Multiple Criteria In Excel

Have you ever come across a task to calculate the average of the numbers with respect to multiple criteria? Are you tired of doing this cumbersome task manually? Are you willing to do this task smartly in just a matter of seconds? Then congratulations because you have just landed on the right article.

In this article, you will get to know the easiest way to calculate the average of the numbers with respect to multiple criteria in no time.

So read this article till the end, and let’s dive into it;

## General Formula

`=AVERAGEIFS(values,range1,criteria1,range2,criteria2)`

### Explanations for Syntax:

Before learning how to use the above formula, let’s first understand the role of each syntax in quickly calculating the average of the numbers concerning multiple criteria.

• `AVERAGEIFS`: In Excel, this function returns the average of a set of input values that meet multiple criteria. Learn more about the AVERAGEIFS Function.
• `Comma symbol` (,): It acts as a separator that helps to separate a list of values.
• `Parenthesis` (): The main function of this symbol is to group the elements.
• `Range`: It represents the input value from your worksheet.
• `Criteria`: The specific criteria or values used to calculate the average.

## Explanation

You can use the AVERAGEIFS function to average numbers based on multiple criteria. In the example, the formula in H2  is as follows:

`=AVERAGEIFS(sales,product,E2,region,F2)`

Where “sales” (C2:C9), “product” (A2:A9), and “sales” (C2:C9) are named ranges.

The AVERAGEIFS function, like the COUNTIFS and SUMIFS functions, is intended to handle multiple criteria that are entered in [range, criteria] pairs. AVERAGEIFS’s behavior is fully automatic as long as this information is provided correctly.

In the example, we have product values in column E and region values in column F. We use these values directly by using cell references as criteria.

The first argument holds the range of values to average:sales range

To limit the calculation by product, we provide:product, E2

We use the following formula to limit the calculation by region:region, F2

The answer in cell H2 is 396:

Now, let’s go to the 2nd example in which we want to average the product of “excel” and the sales greater than 300. Please follow these instructions:

Enter the formula given below into a blank cell:

`=AVERAGEIFS(sales,product,E2,sales,F2)`

(product range is the data that contains the criteria1, sales range is the range which we want to calculate the average, E2 and F2 are the criteria1 and criteria 2), then press the Enter key to get the desired result. Take a look at this screenshot:

Note: If you need more than two criteria, add the criteria ranges and criteria you require as follows:

`=AVERAGEIFS(sales,product,E2,region,F2,sales,G2)`

Note: the first criteria range is product and E2, the second criteria range is region and F2 and the third criteria is sales and G2 and the range you want to average the values is the criteria, sales range.

For Better understanding, we are adding up the 3rd example as follows:

• Consider the example below to calculate the average of numbers based on multiple criteria.
• The image below will show the input values for Columns B, C, and D.
• Next, enter the given formula in the formula bar section.
• Finally, we’ll see the result in cell H3.

## Summary

This article explains how to use the formulas in Excel to calculate the average of numbers with multiple criteria. I hope you found this article interesting. If you have any ideas, please share them with us.

### Related Functions

• Excel AVERAGEIFS function
The Excel AVERAGEAIFS function returns the average of all numbers in a range of cells that meet multiple criteria.The syntax of the AVERAGEIFS function is as below:= AVERAGEIFS (average_range, criteria_range1, criteria1, [criteria_range2, criteria2],…)….
• Excel COUNTIFS function
The Excel COUNTIFS function returns the count of cells in a range that meet one or more criteria. The syntax of the COUNTIFS function is as below:= COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

## How to Sum if Equal to Many Items or A Range in Excel

If we want to sum numbers from a range with criteria, we often select SUMIF of SUMIFS functions as the first choice to create a formula. The criteria can be a number or an array or a collection of some items. In this article, we will show you to sum numbers based on a range of items. In fact, there are many functions can be applied to help us to figure out this problem, and in real life based on different situations, you can select different functions. But in this article, we will show you the way to resolve this problem by using SUMIF/SUMPRODUCT functions combination. We will introduce you the syntax, arguments and basic usage of these functions, and let you know the working process of our formula.

## EXAMPLE

Refer to the left-hand side table, we can see some kinds of fruits are listed in “Fruit” column. And amounts are listed in “Amount” column. In the right-hand side table, we list the items we want to sum amounts for them. In previous instance, we have learned how to sum amounts for one item only, for example, we can apply SUMIF of SUMIFS function to create a formula “=SUMIF(A2:A13,D2,B2:B13)” to sum amounts for “Apple”.

But in this case, sum criteria contain two items “Apple” and “Orange”, and the total amount is recorded in one cell and it covers the two items. Thus, only applying SUMIF function cannot figure out our problem, even we can create a formula like “=SUMIF(A2:A13,D2,B2:B13)+SUMIF(A2:A13,D3,B2:B13)” to calculate total amount, it is clear but complex, and when there are multiple items, the formula will be very long. So, we need to find a proper way to sum. In fact, there are many ways to sum numbers based on criteria contain a range reference, for this instance, we will show you to sum amounts for many items by SUMPRODUCT and SUMIF functions.

Before designing a formula, we can name range references.

Select range A2:A13, in name box enter “Fruit”, then press Enter.

Select range B2:B13, in name box enter “Amount”, then press Enter.

Select range D2:D3, in name box enter “Items”, then press Enter.

## CREATE A FORMULA with SUMPRODUCT & SUMIF FUNCTIONS

### 1.STEPS

Step 1: In E2, enter the =SUMPRODUCT(SUMIF(Fruit,Items,Amount)).

You can enter named range like “Fruit” into your formula directly, or you can also select the range by dragging the handle as well.

Step 2: Press Enter after typing the formula.

We can see in column A, cell A2, A3, A6, A8, A9 and A11 meet our criteria, so we just need to sum numbers from B2, B3, B6, B8, B9 and B11, so the total amount is 5000+6150+5500+4000+8500+7500=36650. The formula works correctly.

### 2.FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF. It can sum up numbers by specific condition.

Syntax:

` =SUMIF(criteria_range, criteria, sum_range)`

SUMPRODUCT function can return sum of products.

Syntax:

` =SUMPRODUCT(array1, [array2], [array3], ...)`

For both SUMPRODUCT and SUMIF functions, they support wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like ‘>’,’<’. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

### 3.ALL ARGUMENTS

In this case, SUMIF is included in SUMPRODUCT formula as its argument.

SUMIFS – RANGE

Range reference “Fruit” is the criteria range.

In the formula bar, select “Fruit”, press F9, values in this range are expanded in an array.

SUMIFS – CRITERIA

As we want to sum amount for “Apple” and “Orange” together, so the criteria here is a range reference “Items”.

In the formula bar, select “Items”, press F9, items in this range are expanded in an array.

As we said we can use “=SUMIF(A2:A13,D2,B2:B13)+SUMIF(A2:A13,D3,B2:B13)” to sum total for both two items, in fact we can use an array constant like {D2;D3} to shorten the formula.

Note: an array constant can provide a set of values, and the values are hard-coded. It is frequently used in array formula.

SUMIF – SUM RANGE

Range reference “Amount” is the “sum range”.

In the formula bar, select ‘Amount’, press F9, amounts in this range are expanded in an array.

### 4.HOW THE FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference, in the formula bar, the formula is displayed as:

`=SUMPRODUCT(SUMIF({"Apple";"Orange";"Banana";"Peach";"Orange";"Banana";"Apple";"Orange";"Banana";"Apple";"Grape";"Banana"},{"Apple";"Orange"},{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000}))`

A pair of criteria range and criteria:

`{"Apple";"Orange";"Banana";"Peach";"Orange";"Banana";"Apple";"Orange";"Banana";"Apple";"Grape";"Banana"}`

– Criteria Range

{“Apple”;”Orange”} – Criteria (more than one items)

If items from criteria range match either of criteria “Items”, “True” will be recorded in the array, otherwise, “False” will be recorded. So, after comparing with “Apple” and “Orange” separately, the original criteria range is split to two new arrays which only contains “True” and “False”.

{True;False;False;False;False;False;True;False;False;True;False;False} – compare with “Apple”

{False;True;False;False;True;False;False;True;False;False;False;False} – compare with “Orange”

For the following logical operation, “True” is coerced to “1” and “False” is coerced to “0”. So, values in the array are converted to numbers:

{1;0;0;0;0;0;1;0;0;1;0;0} – compare with “Apple”

{0;1;0;0;1;0;0;1;0;0;0;0} – compare with “Orange”

Sum amount of “Apple” refer to sum range:

{1;0;0;0;0;0;1;0;0;1;0;0} – filter “Apple” in criteria range

{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000} – sum_range

Multiply the values from the two arrays in the same position. Then we save the products in an array:

{5000;0;0;0;0;0;4000;0;0;7500;0;0}

Sum amount of “Orange” refer to sum range:

{0;1;0;0;1;0;0;1;0;0;0;0} – filter “Orange” in criteria range

{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000} – sum_range

Multiply the values from the two arrays in the same position. Then we save the products in an array:

{0;6150;0;0;5500;0;0;8500;0;0;0;0}

SUMPRODUCT function will sum all products: (5000+4000+7500)+(6150+5500+8500)=16500+20150=36650

In this case if we directly select SUMIF part and press F9, array {16500;20150} is returned.

We can also use SUM function to replace SUMPRODUCT function in this case.

## SUMMARY

1. SUMIF function can handle only one pair of criteria range and criteria. Sum range is the last argument among all arguments.

2. SUMPRODUCT function can return the sum of products.

3. They all support wildcards, logical operators.

### Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

## How to Sum if Equal to X or Y in Excel

In daily work, if we want to sum numbers from a range, and only sum the numbers which being equal to X or Y in the range, we can create a formula with Excel build-in functions to get the result. In fact, there are many functions can be applied to help us figure out the problem. In this article, we will show you three different ways to resolve this problem by using SUMIFS/SUMIF/SUMPRODUCT functions. We will introduce you the syntax, arguments and basic usage of these functions, and let you know the working process of different formulas.

## EXAMPLE

Refer to the left-hand side table, we can see some tasks in software test progress are listed, for each task, there are several related test cases, for example, for task “Install”, there are 3 test cases assigned to it. In “Status” column, there are three status “Pass”, “Fail” and “Block”. For the tasks which status is “Fail” or “Block”, we need to re-test the related test cases, so we need to know how many test cases we need to select and execute in next regression test. Now, we need to calculate the re-run test case number. There are many ways to sum numbers based on specific criteria, for this instance, we will show you how to sum numbers by formula with different functions SUMIF/SUMIFS/SUMPRODUCT. We will enter different formulas into H2, H3, H4 of “Total case” column accordingly in the right-hand side table.

Before designing a formula, we can name range references.

Select range C2:C9, in name box enter ‘Case’, then press Enter.

Select range D2:D9, in name box enter ‘Status’, then press Enter.

## CREATE A FORMULA with SUM & SUMIFS FUNCTIONS

### 1.STEPS

Step 1: In H2, enter the =SUM(SUMIFS(Case,Status,{“Block”,”Fail”})).

`=SUMPRODUCT(C2:C9*(D2:D9={"Block","Fail"}))`

Step 2: Press Enter after typing the formula.

We can see in column D, cell D3, D5 and D8 meet our criteria, so we just need to calculate case numbers from C3, C5 and C8, so the total case number is 2+1+2=5. The formula works correctly.

### 2.FUNCTION INTRODUCTION

SUM function can add numbers together. It adds all supplied values from a range or a formula.

Syntax:

`=SUM (number1, [number2], [number3], ...)`

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

Syntax:

`=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

For both SUM and SUMIFS function, they support wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like ‘>’,’<’. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

### 3.ALL ARGUMENTS

In this case, SUMIFS is included in SUM formula, it is the only argument of SUM function, its returned values should be accumulated by SUM function.

SUMIFS – SUM RANGE

In our instance, range reference ‘Case’ is the ‘sum range’. Test case numbers are listed in this field.

In the formula bar, select ‘Case’, press F9, values in this range are expanded in an array.

SUMIFS – CRITERIA RANGE 1

Range reference ‘Status’ is the criteria range. In this instance we have only one criteria range. We have only three status “Pass”, “Fail” and “Block”.

In the formula bar, select “Status”, pree F9, values in this range are expanded in an array.

SUMIFS – CRITERIA 1

As we want to calculate total test case number for tasks which “status=fail” or “status=block”, so if one condition is matched, test case number will be recorded and accumulated. So, we will supply two criteria “Fail” and “Block” in this case, they are expanded saved in one array, a comma is added between them to split the two criteria. As SUMIFS function supports texts and wildcards, but they should be enclosed into double quotes “”, so for criteria 1, we enter {“Fail”,”Block”}.

Actually, the two criteria can be split to two “criteria1” in two different SUMIFS formulas, for example, if we enter =SUM(SUMIFS(Case,Status,”Fail”),SUMIFS(Case,Status,”Block”)) in H2, we can get the same result.

This formula is clear but looks a little bit complex, so we use an array constant with two elements to shorten the formula.

### 4.HOW THE FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference, in the formula bar, the formula is displayed as:

`=SUM(SUMIFS({3;3;2;1;1;2;2;2},{"Pass";"Pass";"Block";"Pass";"Fail";"Pass";"Pass";"Fail"},{"Block","Fail"}))`

If value from the criteria range can match either criteria, “True” will be recorded in this position, otherwise, “False” will be saved instead.

{“Pass”;”Pass”;”Block”;”Pass”;”Fail”;”Pass”;”Pass”;”Fail”} – Criteria Range

{“Block”,”Fail”} – Criteria Collection

So, after comparing, we can get two new arrays based on different criteria:

{False;False;True;False;False;False;False;False} – for criteria “Block”

{False;False;False;False;True;False;False;True} – for criteria “Fail”

For the following logical operation, “True” is coerced to “1” and “False” is coerced to “0”. So above arrays are converted to two arrays only contain numbers “1” and “0”.

{0;0;1;0;0;0;0;0} – for criteria “Block”

{0;0;0;0;1;0;0;1} – for criteria “Fail”

Now, we have below two pairs of arrays:

{3;3;2;1;1;2;2;2} – sum_range

{0;0;1;0;0;0;0;0} – for criteria “Block”

{3;3;2;1;1;2;2;2} – sum_range

{0;0;0;0;1;0;0;1} – for criteria “Fail”

In the two groups, for the elements in the same position, multiply the two elements. Then we can get two new arrays.

{0;0;2;0;0;0;0;0} – for criteria “Block”

{0;0;0;0;1;0;0;2} – for criteria “Fail”

Add all products in above two arrays separately, we get 2 and 3. Actually, if ignore above analysis, directly select SUMIFS part and press F9, a simple array {2,3} is returned.

Now, the SUM function will add the two numbers together, then we get 5 as last.

## CREATE A FORMULA with SUM & SUMIF FUNCTIONS

SUMIF function is similar with SUMIFS, they have the same arguments, the difference is it can only handle one criteria range with one criterion, and sum range is listed in the end.

`Syntax: =SUMIF(criteria_range, criteria, sum_range)`

In this case, all arguments are the same for SUMIF and SUMIFS, as SUMIF only supports one group of criteria range and criteria, so we need to create two SUMIF formulas, and use SUM function to sum two returned values.

In H3, enter the formula =SUM(SUMIF(Status,{“Block”,”Fail”},Case)).

Press Enter to get result. We can see we get the same result.

## CREATE A FORMULA with SUMPRODUCT FUNCTION

Different with SUMIFS or SUMIF function, SUMPRODUCT function can directly return proper result and ignore SUM function.

### 1.STEPS

Step 1: In H4, enter the =SUMPRODUCT(Case*(Status={“Block”,”Fail”})).

Step 2: Press Enter after typing the formula.

### 2.FUNCTION INTRODUCTION

SUMPRODUCT function can be seen as SUM+PRODUCT.

Syntax:

`=SUMPRODUCT(array1, [array2], [array3], ...)`

### 3.ALL ARGUMENTS

SUMPRODUCT – ARRAY1

In this case, we have only one array, it is a formula “Case*(Status={“Block”,”Fail”})”.

In the formula bar, select “Status”, pree F9, values in this range are expanded in an array.

Compare each status from range ‘Status’ with the criteria, as there are eight elements in criteria range, and two elements in criteria, so after comparing, system will return a two-dimension array (2*8) in the formula bar.

Just select ({“Pass”;”Pass”;”Block”;”Pass”;”Fail”;”Pass”;”Pass”;”Fail”}={“Block”,”Fail”}), then press F9. Verify that comparative result is displayed.

Select “Case”, then press F9, all values from range “Case” are expanded.

### 4.HOW THE FORMULA WORKS

After expanding values in each range reference, in the formula bar, the formula is displayed as:

`=SUMPRODUCT({3;3;2;1;1;2;2;2}*{FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE})`

Notes: For the following logical operation, “True” is coerced to “1” and “False” is coerced to “0”. The formula is updated to =SUMPRODUCT({3;3;2;1;1;2;2;2}*{0,0;0,0;1,0;0,0;0,1;0,0;0,0;0,1}) in calculation progress.

In the formula bar, select {3;3;2;1;1;2;2;2}*{FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE} and press F9. We can see that one new array consists of products of previous two arrays are listed.

Select =SUMPRODUCT({0,0;0,0;2,0;0,0;0,1;0,0;0,0;0,2}), press F9 to do the last step – SUMPRODUCT function will add products in the array together. As 2+1+2=5, so we get 5 by this formula.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. SUMIF function can handle only one pair of criteria range and criteria. Sum range is the last argument among all arguments.

3. SUMPRODUCT function can return the sum of products.

4. They all support wildcards, logical operators.

### Related Functions

• Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

## How to Sum Last N Days in Excel

In our daily life, we may want to sum amounts or sales for a specific period, for example in last N days. Sum numbers in Excel is easy to run, we can apply SUM function. But if we want to sum numbers based on criteria, we can SUMIF or SUMIFS function, compare with SUMIF function, SUMIFS can handle multiple criteria effectively.

In this article, we will provide a simple case to show you the way we use SUMIFS function to sum amounts for different items in last 3 days. We will introduce you the syntax, arguments of SUMIFS function, create a formula to sum numbers per our demand. We will also split the formula to several parts and let you know how the formula works step by step. After reading the article, you may have a simple understanding about SUMIFS function and you can use it to sum in last n days.

## EXAMPLE

Refer to the left-hand side table, we can see some kinds of fruits and their amounts on different dates are listed, total three columns. Based on dates, we can see there are four groups of “Apple, Orange and Banana”.

In the right-hand side table, in “Fruit” column, we list the three kinds of fruits in the same column but different cells. And in the adjacent “Total” column, total amount will be calculated and listed in cells properly for each fruit accordingly. We need to enter a formula into F2 to calculate total amount automatically. As we want to just create one formula which also can be applied in cell F3 and F4 directly, so we need the formula can adjust cell reference and range reference automatically and return the correct result based on adjusted references properly.

As we want to sum amounts for different fruit, we have criteria “Fruit=Apple/Orange/Banana” now; Besides, if we want to sum amounts for each fruit in a specific period, for example only sum amounts in last 3 days (today, yesterday and the day before yesterday, current date is 9-May-21 which is on the top in Date column), we will have new criteria “in last 3 days” now. To create a formula to resolve this issue, we can apply SUMIFS function here as it can hand the case with multiple criteria.

## FORMULA – SUMIFS FUNCTIONS

Step 1: In F2, enter the formula =SUMIFS(\$C\$2:\$C\$13,\$B\$2:\$B\$13,”>=”&TODAY()-2,\$A\$2:\$A\$13,\$E2).

Step 2: Press Enter after typing the formula.

We can see \$15,000.00 is returned. The format is correct. Actually, if you didn’t set the format for cell F2 before, only “15000” is returned. Before entering the formula, you need to focus on this cell, and click ‘Dollars’ icon under ‘Number’ section, and also double click on ‘Increase Decimal’ icon to add two decimal places for the returned number.

Now, let’s check the result. in column A, only cell A2, A5, A8 and A11 can meet our criteria “Fruit=Apple”, as we only sum the amounts in last 3 days, so A11 is ignored. We just need to sum amounts from C2, C5 and C8, so the total number is 5000+6000+4000=15000. The formula works correctly.

By the way, if you are confused with cell reference and range refence in the formula, we can also define range name before creating the formula. See steps below:

1. Select A2:A13, then in Name Box enter “Fruit”, press Enter Define reference B2:B13 as “Date”, C2:C13 as “Amount”. See screenshot below.

2. Then create the formula =SUMIFS(Amount,Date,”>=”&TODAY()-2,Fruit,\$E2) in F2. You can enter “Amount” directly in the formula instead of selecting a range reference.

We can get the same result.

## FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

For SUMIFS function, is supports wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like “>”,”<”. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

Today function can return current date. It doesn’t have any argument.

ALL ARGUMENTS

The formula is =SUMIFS(\$C\$2:\$C\$13,\$B\$2:\$B\$13,”>=”&TODAY()-2,\$A\$2:\$A\$13,\$E2). Refer to above SUMIFS function introduction, we will split it to several parts.

SUMIFS – SUM RANGE

\$C\$2:\$C\$13 is the ‘sum range’ in this case.

We add \$ before row and column index to make this range as absolute references. When applying the formula to other cells, this range is locked.

Select \$C\$2:\$C\$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.

SUMIFS – CRITERIA RANGE1

\$B\$2:\$B\$13 is the first criteria range. We have two criteria ranges in this instance. The first one provides date criteria.

Select \$B\$2:\$B\$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.

We found that dates are converted from Date format to General format: {44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322}. Each five digits number represents a date.

SUMIFS – CRITERIA1

“>=”&TODAY()-2 is the criteria1.

As we want to sum amounts in last 3 days, except today, we only need to sum amounts on yesterday and the day before yesterday, TODAY()-2 can represent the day before yesterday, and we use “>=” to make sure this day is included in calculation.

Logical operator “>=” is quoted in “”;

Today() returns the number which can represent current date; Actually TODAY function will return current date in Date format, but we can change it to General format by selecting General in Number Format dropdown list in Number section.

So, =TODAY()-2 is equal to 44323.

Select “>=”&TODAY()-2 from formula in the formula bar, the press F9, “>=”&44323 is displayed.

SUMIFS – CRITERIA RANGE2

\$A\$2:\$A\$13 is the second criteria range. The second one provides fruit references.

Select \$A\$2:\$A\$13 from formula in the formula bar, the press F9, values in this range are expanded in an array.

SUMIFS – CRITERIA2

\$E2 is the criteria2.

In E2, one kind of fruit “Apple” is recorded. In this case, we can omit \$ actually as formula is only applied in F3 and F4 which are in the same column with F2. But if we want to copy this formula in other cells in different horizontal levels, column E will be adjusted in the formula automatically due to position changed, so we can add \$ before column to lock it.

Select \$E2 from formula in the formula bar, the press F9, “Apple” is displayed.

## HOW THE FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference, in the formula bar, the formula is displayed as:

=SUMIFS({5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000},{44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322},”>=”&44323,{“Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”},”Apple”)

There are two pairs of criteria range and criteria.

{44325;44325;44325;44324;44324;44324;44323;44323;44323;44322;44322;44322} – Criteria Range1

“>=”&44323 – Criteria1

And

{“Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”;”Apple”;”Orange”;”Banana”} – Criteria Range2

“Apple” – Criteria2

If values from each criteria range are matched with the criteria, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new criteria range:

{True;True;True;True;True;True;True;True;True;False;False;False} – after comparing with criteria1

{True;False;False;True;False;False;True;False;False;True;False;False} – after comparing with criteria2

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above two arrays are converted to below arrays which consist of numbers “1” and “0”.

{1;1;1;1;1;1;1;1;1;0;0;0} – criteria range1

{1;0;0;1;0;0;1;0;0;1;0;0} – criteria range2

As we sum range need to meet the two criteria both, so we just keep the intersection of the two arrays. If in the same position, 1 is recorded for both, keeps 1, otherwise keeps 0.

{1;0;0;1;0;0;1;0;0;0;0;0} – the intersection of the two criteria range

Now, we have below arrays:

{5000;6150;4500;6000;5500;6500;4000;8500;9000;7500;8500;8000} – sum range

{1;0;0;1;0;0;1;0;0;0;0;0} – criteria range

Elements are one-to-one matched in two arrays. Multiply the two elements in the same position in the array. Then we can get a new array.

{5000;0;0;6000;0;0;4000;0;0;0;0;0}

Now, drag down the formula to fill F3 and F4. We can get total amount in last 3 days for each kind fruit properly.

## SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators.

5. TODAY function only returns current date. It has no argument.

### Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)
• Excel TODAY function
The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…

## How to Sum if Contains an Asterisk

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If we want to sum cells with special character only, for example sum cells which contains an asterisk “*”, we can use “*~**” to represent the cells. In this article, we will introduce you the syntax, arguments of SUM and SUMIFS function, and why we use “*~**” to stands for the selected items. We will also let you know how the formula works step by step. After reading the article, you may have a simple understanding about SUMIFS function and the usage of asterisk (*).

## EXAMPLE

Refer to the left-hand side table, we can see some kinds of drinks and their amounts are listed. Some items are marked with an asterisk (*) in its name for example “Coffee *”, it represents an unknow type of coffee or any coffee is ok. In the right-hand side table, in “Item” column, we can list the kind of drink we want, and in the “Total” column, total amount will be calculated and listed in E2 properly based on the selected items. We need to enter a formula into E2 to calculate total amount automatically. In this case, the items are that marked with an asterisk, now we need to create a formula that can calculate all amounts for those drinks marked with an asterisk. To create a formula, we can apply SUMIFS function here.

## FORMULA – SUM & SUMIFS FUNCTIONS

Step 1: In E2, enter the formula

` =SUMIFS(B2:B12,A2:A12,"*~**").`

Step 2: Press Enter after typing the formula.

We can see \$26.00 is returned. The format is correct. Actually, if you didn’t set the format for cell E2 before, only “26” is returned. Before entering the formula, you need to focus on this cell, and click ‘Dollars’ icon under ‘Number’ section, and also double click on ‘Increase Decimal’ icon to add two decimal places for the returned number.

Now, let’s check the result. in column A, cell A4, A8, A11 and A12 meet our demand, so we just need to sum amounts from B4, B8, B11 and B12, so the total number is 4+4+10+8=26. The formula works correctly.

### FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

Syntax:

`=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

[] part can be omitted.

For SUMIFS function, is supports wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators like “>”,”<”. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

Notes:

In this case, as an asterisk stands for one or more characters by itself, so we need to add a “~” before it to make it only stands for special character “*” literally, then Excel can handle it properly and treat it as an asterisk in formula working process. As there might be some other characters before or after special character “*” in items, so we need to add “*” before and after “~*”. Finally, to represent an item contains an asterisk, we use “*~**” to stand for it.

### ALL ARGUMENTS

The formula is =SUMIFS(B2:B12,A2:A12,”*~**”). Refer to above SUMIFS function introduction, we will split it to several parts.

SUMIFS – SUM RANGE

B2:B12 is the ‘sum range’ in this case.

Select B2:B12 from formula in the formula bar, the press F9, values in this range are expanded in an array.

SUMIFS – CRITERIA RANGE

A2:A12 is the criteria range. We have only one criteria range in this instance.

Criteria range contains some kinds of drinks, some of them contain an asterisk, we need to pick them up.

Select A2:A12 from formula in the formula bar, the press F9, values in this range are expanded in an array.

SUMIFS – CRITERIA

D2 is the criteria. D2=”*~**”.

As we mentioned before, “~*” can stand for a literal asterisk. “*~**” stands for items that contain one asterisk. If characters are only listed before the asterisk, you can also set “*~*” as criteria.

### HOW THE FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference, in the formula bar, the formula is displayed as:

`=SUMIFS({5;6;4;6;5;6;4;8;9;10;8},{"Latte";"Cappuccino";"Coffee *";"Milk Tea";"Green Tea";"Black Tea";"* Tea";"Orange Juice";"Peach Juice";"* Juice";"Apple * Juice"},"*~**")`

There is one pair of criteria range and criteria.

`{"Latte";"Cappuccino";"Coffee *";"Milk Tea";"Green Tea";"Black Tea";"* Tea";"Orange Juice";"Peach Juice";"* Juice";"Apple * Juice"} – Criteria Range`

{“*~**”} – Criteria

If values from the criteria range are matched with the criteria “marked with an asterisk”, “True” will be recorded and saved, otherwise, “False” will be saved instead. So, after comparing, we can get a new array:

`{False;False;True;False;False;False;True;False;False;True;True}`

– after comparing with criteria

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above array is converted to below array which consists of numbers “1” and “0”.

{0;0;1;0;0;0;1;0;0;1;1} – criteria range “1” means which can meet the criteria

Now, we have below two arrays:

{5;6;4;6;5;6;4;8;9;10;8} – sum range

{0;0;1;0;0;0;1;0;0;1;1} – criteria range

Elements are horizontal-aligned in the two arrays. Multiply the two elements in the same position in the array. Then we can get a new array.

{0;0;4;0;0;0;4;0;0;10;8}

1.In this case, as we have only one pair of criteria range and criteria, we can also apply SUMIF function as well. The difference is SUMIF function only supports one group of criteria range and criteria, and its sum range is listed at the end.

Enter =SUMIF(A2:A12,”*~**”,B2:B12), we can get the same result as applying SUMIFS.

## SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators.

5. To represent an item contains an asterisk, we use “*~**” to stand for it.

### Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

## How to Sum with Criteria and Or Logic in Excel

To add numbers together we need to apply SUM function. And if we want to add numbers based on some conditions, we can add criteria with the help of SUMIFS function, SUMIFS can filter data with multiple criteria effectively. If we want to filter data based on multiple criteria with OR logic, we can add array constant to help us. In this article, we will introduce you the syntax, arguments of SUM and SUMIFS function, and let you know how this formula works to reach your goal. After reading the article, you may have a simple understanding the two functions and array constant construction.

## EXAMPLE

Refer to the left-hand side table, we can see some tasks in software test progress are listed, for each task, there are several related test cases, for example, for task “Install”, there are 3 test cases assigned to it. In “Status” column, there are three status “Pass”, “Fail” and “Block”. For the tasks which status is “Fail” or “Block”, we need to re-test the related test cases, so we need to know how many test cases we need to select and execute in next regression test. Now, we need to calculate the re-run test case number based on the criteria “status=fail or block”, record the result in the right-hand side table “Total case” column accordingly.

In this instance, we need to pick up numbers from fail or block status, then sum them together. To fix this issue by formula, we can apply SUMIFS and SUM functions here.

## FORMULA – SUM & SUMIFS FUNCTIONS

Step 1: In G2, enter the formula

`=SUM(SUMIFS(C2:C9,D2:D9,{"Block","Fail"})).`

Step 2: Press Enter after typing the formula.

We can see in column D, cell D3, D5 and D8 meet our criteria, so we just need to calculate case numbers from C3, C5 and C8, so the total case number is 2+1+2=5. The formula works correctly.

## FUNCTION INTRODUCTION

SUM function can add numbers together. It adds all supplied values from a range or a formula.

Syntax:

`=SUM (number1, [number2], [number3], ...)`

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

Syntax:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

[] part can be omitted.

For both SUM and SUMIFS function, they support wildcards like asterisk ‘*’ and question mark ‘?’, also support logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

### ALL ARGUMENTS

In this case, SUMIFS is included in SUM formula, it is the only argument of SUM function, its returned values should be accumulated by SUM function.

SUMIFS – SUM RANGE

In our instance, C2:C9 is the ‘sum range’. Test case numbers are listed in this field.

In the formula bar, select ‘C2:C9’, press F9, values in this range are expanded in an array.

SUMIFS – CRITERIA RANGE 1

D2:D9 is the criteria range. In this instance we have only one criteria range. We have only three status “Pass”, “Fail” and “Block”.

In the formula bar, select D2:D9, pree F9, values in this range are expanded in an array.

SUMIFS – CRITERIA 1

As we want to calculate total test case number for tasks which “status=fail” or “status=block”, so as long as one condition is met, test case number will be recorded and accumulated. So, we will supply two criteria “Fail” and “Block” in this case, they are expanded saved in one array, a comma is added between them to split the two criteria. As SUMIFS function supports texts and wildcards, but they should be enclosed into double quotes “”, so for criteria 1, we enter {“Fail”,”Block”} finally.

Actually, the two criteria can be split to two “criteria1” in two different SUMIFS formulas, for example, if we enter =SUM(SUMIFS(C2:C9,D2:D9,”Fail”),SUMIFS(C2:C9,D2:D9,”Block”)) in G2, we can get the same result. This formula is clear but looks complex, so we use an array constant with two elements to shorten the formula.

### HOW THE FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

After expanding values in each range reference, in the formula bar, the formula is displayed as:

`=SUM(SUMIFS({3;3;2;1;1;2;2;2},{"Pass";"Pass";"Block";"Pass";"Fail";"Pass";"Pass";"Fail"},{"Fail","Block"})`

If value from the criteria range can match either value in criteria collection, “True” will be recorded in this position, otherwise, “False” will be saved instead.

{“Pass”;”Pass”;”Block”;”Pass”;”Fail”;”Pass”;”Pass”;”Fail”} – Criteria Range

{“Fail”,”Block”} – Criteria Collection

So, after comparing, we can get two new arrays based on different criteria:

{False;False;False;False;True;False;False;True} – for criteria “Fail”

{False;False;True;False;False;False;False;False} – for criteria “Block”

For the following logical operation, “True” is coerced to ‘1’ and ‘False’ is coerced to ‘0’. So above arrays are converted to two arrays only contain numbers “1” and “0”.

{0;0;0;0;1;0;0;1} – for criteria “Fail”

{0;0;1;0;0;0;0;0} – for criteria “Block”

Now, we have below two pairs of arrays:

{3;3;2;1;1;2;2;2} – sum_range

{0;0;0;0;1;0;0;1} – for criteria “Fail”

{3;3;2;1;1;2;2;2} – sum_range

{0;0;1;0;0;0;0;0} – for criteria “Block”

In the two groups, for the elements in the same position (in vertical alignment), multiply the two elements. Then we can get two new arrays.

{0;0;0;0;1;0;0;2}

{0;0;2;0;0;0;0;0}

Add all products in above two arrays separately, we get 3 and 2, actually, SUMIFS function will return {3,2} after calculation.

Now, the SUM function will add the two numbers together, that’s why the formula finally returns correct result 5. You can verify this before expanding values in the formula bar, for example, in the formula bar, select the whole formula SUMIFS(C2:C9,D2:D9,{“Fail”,”Block”}), then press F9, you can get =SUM({3,2}) in the formula bar.

1.In this case, if SUM function is omitted, we will get incorrect result, only 3 is displayed.

In fact, in this case SUMIFS function only returns an array contains two elements, it cannot add the two elements together, so we need to add a SUM function to add them.

2. If we want to add more criteria in this case, for example except status, we also add criteria “Software Test=Start Service” or “Software Test=Stop Service”, then we can add new criteria range and new criteria.

Enter

`=SUM(SUMIFS(C2:C9,D2:D9,{"Fail","Block"},B2:B9,{"Start Service";"Stop Service"}))`

into G2, formula returns 1 as result.

In this formula, we can see that in the first array constant, a comma is displayed between the two values, but in the second array constant, a semi-colon is displayed instead of a comma. That’s because the two criteria are expanded in two array constants, each array contains two elements, to filter data based on the two criteria, we need one of them as a row array, and the other one as a column array, then after executing the formula, it will return a two dimensional array.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators.

### Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)

## How to Subtotal Values for Groups and Only Keep One Subtotal for A Group in Column

Sometimes we may meet the case that subtotal values for different groups and only record a subtotal value for one group in a column, for other cells in this group, keep them blank. Today we will introduce you how can we subtotal values for groups and record subtotal values only in specific cells.

We can get subtotal through Subtotal function or SUM function in simple cases, but per different requirement, we need to apply different functions or combinations. Today we will provide a formula contains three functions IF/COUNTIF/SUMIFS to solve this issue. Through demonstrate a simple instance, we will introduce you the syntax, arguments of these functions, and let you know how the formula works step by step. After reading the article, you can think about if there are some other ways to solve this problem.

## EXAMPLE

Refer to above table, we can see that T-shirts are grouped by colors, and amounts for weeks are separately listed. Our expectation is “get subtotal for each product and record them in proper cell”, see example below:

We want to 1) save the subtotal values in the cells which background is filled in light orange. 2) For the other cells in this column should keep blank. 3) By the way, we also want to enter only one formula into D2, then though dragging the handle down to fill other cells, subtotal in D5 and D8 should also be calculated properly.

Can we create a formula that can cover above three conditions? Actually, yes.

In this instance, with the help of IF, COUNTIF and SUMIF functions, we can calculate subtotal for each product by only one formula properly.

## FORMULA

Step 1: Select B2:B10, then in Name Box define a new name for this range, for example ‘Product’.

Step 2: Select C2:C10, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In D2, enter the formula =IF(COUNTIF(\$B\$2:B2,B2)=1,SUMIF(\$B\$2:\$B\$10,B2,\$C\$2:\$C\$10),””).

NOTE: In step#1 and step#2 we defined “Product” and “Amount” two ranges, when entering the formula, after typing “Amou…”, defined range “Amount” is auto loaded. User defined range can be seen as absolute reference, so when copy formula to other cells, the reference will not be adjusted automatically. If we use range reference B2:B10 in the formula, we need to add “\$” to lock the range.

Step 4: Press Enter after typing the formula. A number is returned.

We can see in column D2, subtotal \$450 is returned, it is equal to 100+150+200, the total of C2:C4. The formula is correct.

Step 5: Drag the handle down to fill other cells.

Verify that subtotal values are calculated correctly for each product. Besides, the subtotal is only recorded in the first row of each group, others keep blank.

## FUNCTION INTRODUCTION

In this formula we create a formula with IF function which applies COUNTIF and SUMIF functions as its arguments.

IF function will run a logical test first, and based on the result ‘True’ of ‘False’, it chooses to which branch to execute.

For IF function, the syntax is:

=IF (logical_test, [value_if_true], [value_if_false])

———————————————————————————————-

COUNTIF function can used for counting cell number based on different criteria.

For COUNTIF function, the syntax is:

=COUNTIF (range, criteria)

———————————————————————————————-

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range])

For above three functions, they all allow logical operators like “>”,”>”,”<>” and wildcards like “*”,”?”.

## FUNCTION ARGUMENTS EXPLANATION

For If function:

Logical_test: COUNTIF(\$B\$2:B2,B2)=1

value_if_true: SUMIF(\$B\$2:\$B\$10,B2,\$C\$2:\$C\$10)

value_if_false: “” – nothing returns

——————————————————————————————————————–

For COUNTIF function:

Range: \$B\$2:B2

Criteria: B2

For range \$B\$2:B2 is an expanding range. Start cell is B2, and the end cell is not fixed.

Select range and criteria separately in the formula bar, and press F9 to convert cell reference to real values. See screenshot below:

——————————————————————————————————————–

For SUMIF function:

Range: \$B\$2:\$B\$10

Criteria: B2

Sum Range: \$C\$2:\$C\$10

Select above arguments, press F9 to convert cell reference to real values. See screenshot below:

## HOW THIS FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above steps, the formula is converted to below format in the formula bar.

`=IF(COUNTIF("T-shirt (Red)","T-shirt (Red)")=1,SUMIF({"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Black)";"T-shirt (Black)";"T-shirt (Black)"},"T-shirt (Red)",{100;150;200;160;170;180;150;200;220}),"")`

——————————————————————————————————————–

In IF function, the core part is logical test part, its returned value determines which branch we will go to.

Logical_test:

`COUNTIF("T-shirt (Red)","T-shirt (Red)")=1`

For this part, range “T-shirt (Red)” just contains one criteria “T-shirt (Red)”, so COUNTIF(“T-shirt (Red)”,”T-shirt (Red)”)=1, the formula COUNTIF(“T-shirt (Red)”,”T-shirt (Red)”)=1 is ‘True’. So we go to ‘value_if_ture’ and ignore ‘value_if_false’.

value_if_true:

`SUMIF({"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Black)";"T-shirt (Black)";"T-shirt (Black)"},"T-shirt (Red)",{100;150;200;160;170;180;150;200;220})`

For this part, we have one pair of criteria range and criteria:

criteria range:

`{"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Red)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Blue)";"T-shirt (Black)";"T-shirt (Black)}`

criteria:

`"T-shirt (Red)"`

Compare each value in the array with criteria, we get a new array that only contains ‘True’ and ‘False’.

`{True;True;True;False;False;False;False;False}`

Convert True to 1, False to 0.

{1;1;1;0;0;0;0;0}

Now, we have below two arrays in the formula:

Sum Range:

`{100;150;200;160;170;180;150;200;220}`

{1;1;1;0;0;0;0;0}

We list the two arrays in two rows, multiply the two numbers in the same column, and save their products in another row, sum all products together, we can get 450 at last.

## NOTE

We noticed that after typing the formula and dragging the handle down to fill the ‘Subtotal’ column, not all the cells have a sum value, some cells keep blank as we expect. Why the formula returns an empty value after coping it to other cells?

In cell D3, the formula is automatically adjusted to =IF(COUNTIF(\$B\$2:B3,B3)=1,SUMIF(Product,B3,Amount),””) after coping and pasting.

In logical test part, CONTIF(\$B\$2:B3,B3) is equal to COUNTIF({“T-shirt (Red)”;”T-shirt (Red)”},”T-shirt (Red)”).

Obviously, this formula returns 2, which is not equal to 1, so IF function returns ‘valus_if_false’ value, which is “” an empty value in this case. Above all, in D3, the subtotal is blank.

## SUMMARY

1. To subtotal values we can apply different functions together. In this case we apply IF function core formula, and a formula contains COUNTIF function as logical test, SUMIFS function as ‘True’ part returned value.

2. For above three functions, they all allow logical operators like “>”,”>”,”<>” and wildcards like “*”,”?”.

### Related Functions

• Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
• Excel COUNTIF function
The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)
• Excel SUBTOTAL function
The Excel SUBTOTAL function returns the subtotal of the numbers in a list or database. The syntax of the SUBTOTAL function is as below:= SUBTOTAL (function_num, ref1, [ref2])….

## How to Sum by Formula If Cells Are Not Blank in Criteria Range

Sometimes we may meet the cases that some blank cells exist in criteria range or sum range. In most situations we will ignore them, so we need to filter data by ‘not blank/not empty’. Today we will introduce you how can we sum only for not blank cells if some are blank in criteria range.

Through demonstrate a simple instance, we will introduce you the syntax, arguments of SUMIFS function, and let you know how the formula works step by step and finally reach your goal. After reading the article, you may have a simple understanding of SUMIFS function. We will also introduce you another method to resolve this issue by SUMIF function at the end.

## EXAMPLE

Refer to above table, we can see that some different colors of T-shirt are listed in “Product” column. For these T-shirts, due to different colors, some of them are available at a discount. Amount for each color T-shirt is listed in “Amount” column accordingly.

In this instance, we want to calculate total amount for the products which have a discount on sale. So, we need to filter data in ‘Discount’ column based on the criteria that ‘discount field is not blank’. Then we can sum up filtered values in “Amount” column. To resolve this issue by formula, we can apply SUMIFS or SUMIF functions.

## FORMULA – SUMIFS FUNCTION

Step 1: Select B2:B7, then in Name Box define a new name for this range, for example ‘Discount’.

Step 2: Select C2:C7, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In E2, enter the formula =SUMIFS(Amount,Discount,”<>”).

NOTE: In step#1 and step#2 we defined range name “Discount” and “Amount”, when entering the formula, after typing “Amou…”, defined range “Amount” is auto loaded, you can directly select it from dropdown list. You can also select B2:B7, C2:C7 to fill formula arguments as well.

Step 4: Press Enter after typing the formula. A number is returned.

We can see in column B, except B3 and B7, other cells are filled with ‘Yes’ and not empty, the corresponding amounts are 100 (in cell C2), 120 (in cell C4), 150 (in cell C5) and 130 (in cell C6), so the total amount is 100+120+150+130=500. The formula works correctly.

### SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

SUMIFS function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part. For example, “*A*” means characters or texts are both listed before and after “A”. “A*” means this text is started with A, but ends with others.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

SUMIFS ARGUMENTS EXPLANATION

SUMIFS – SUM RANGE

In our instance, C2:C7 is the ‘sum range’ obviously. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE 1

We have only one criteria range in this case, it is B2:B7. This range records if product has a discount.

In the formula bar, select ‘Discount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA 1

We want to calculate total amount for the products which has a discount. As we mentioned above, SUMIFS function allows logical operators, so just enter “<>” as criteria. “<>” means not equal to, when it is used as criteria, it means “not empty”.

HOW SUMIFS FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, the formula is converted into below format in the formula bar.

=SUMIFS({100;200;120;150;130;230},{“Yes”;0;”Yes”;”Yes”;”Yes”;0},”<>”)

In the formula, there is one pair of criteria range and criteria:

Criteria Range: {“Yes”;0;”Yes”;”Yes”;”Yes”;0}

Criteria: “<>”

If cell is not empty, mark it in bold:

{“Yes“;0;”Yes“;”Yes“;”Yes“;0}

So, for bold texts, record a ‘True’ in the array; otherwise, record a ‘False’. Then we can get a new array:

{True;False;True;True;True;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

{1;0;1;1;1;0}

Now, we have below two arrays:

Sum Range: {100;200;120;150;130;230}

Criteria: {1;0;1;1;1;0} – if product has a discount, 1 is displayed.

We list the two arrays in two rows, multiply the two numbers in the same column, and save their products in another row, sum all products together, we can get 500 at last.

1.In this case, as in ‘Discount’ column, only ‘Yes’ is recorded, you can also update the formula to “=SUMIFS(Amount,Discount,”Yes”)”.

2.The formula also works well if the discount is a real number.

3.In this case, we can also use SUMIF function if cells are not blank.

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range]). Argument in [] is optional. If sum range is omitted, SUMIF will sum up all numbers in range argument. The order of the arguments is different from SUMIFS.

Enter the formula =SUMIF(Discount,”<>”,Amount).

After entering the formula, we can see that SUMIF function also works correctly. Actually, in most situations, SUMIF function can be used instead of SUMIFS function if there is only one pair of criteria range and criteria. If multiple criteria are supplied, you can choose SUMIFS to handle them.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators. ”<>” can represent “not equal to/not empty” in a formula.

### Related Functions

• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

## How to Sum by Formula if Cell Ends with in Excel

We have introduced the method of sum numbers based on criteria “sum if cell begins with” (you can refer to ‘How to Sum Data if Begins with in Excel’ on our website). In this article, we will show you the formula about ‘sum if cell ends with’ with the help of Excel SUMIFS or SUMIF function.

Through demonstrate a simple instance, we will introduce you the syntax, arguments and usage of SUMIFS and SUMIF function, and let you know how this formula works to reach your goal. After reading the article, you may have a simple understanding of SUMIFS/SUMIF function.

## EXAMPLE

Refer to above left-hand table, we can see that there are two columns, one is “Date”, another column is “Amount”. “Date” column lists some dates in year 2020 and 2021. “Amount” column lists the amount for “Date” accordingly.

In this instance, we want to calculate sum of year 2021. So, we need to find out all dates belong to year 2021, and then sum up values in “Amount” column. To find out dates belong to year 2021, through above table we can know that they are ended with 2021, so the filter criteria for “Date” range is “ends with 2021”, after all, we can apply SUMIFS or SUMIF here to fix this issue properly. At first, let’s introduce the usage of SUMIFS formula.

## FORMULA 1 – APPLY SUMIFS FUNCTION

Step 1: Select A2:A7, then in Name Box define a new name for this range, for example ‘Date’.

Step 2: Select B2:B7, in Name Box define a new name for this range, for example ‘Amount’.

Note: Above two steps are optional, you can directly use cell or range reference like A2:A7, B2:B7 in your formula, but for understanding well, we name ranges in advance. When applying formula, user can directly enter “Amount”, “Date” into the formula.

Step 3: In E2, enter the formula =SUMIFS(Amount,Date,”*2021″).

Step 4: Press Enter after typing the formula.

We can see in “Date” column, dates in cell A3, A5 and A7 are ended with Y2021, the corresponding amounts are 200 (in cell B3), 150 (in cell B5), and 230 (in cell B7), so the total is 200+150+230=580. The formula works correctly.

## SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

SUMIFS function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

### SUMIFS ARGUMENTS EXPLANATION

SUMIFS – SUM RANGE

In our instance, B2:B7 is the ‘sum range’ obviously. Amount values are listed in this field. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE 1

A2:A7 is the criteria range. In this instance we have only one criteria range. This range contains different dates in year 2020 and year 2021.

In the formula bar, select ‘Date’, press F9. All dates are displayed in proper order in an array:

SUMIFS – CRITERIA 1

We want to calculate “sum of year 2021”, so we need to find out all dates that ends with year 2021. SUMIFS function supports texts and wildcards, they should be enclosed into double quotes “ “ when applying them, so for criteria 1, we enter “*2021”, “*” means there are some characters before “2021”.

SUMIFS WORKFLOW

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, we can get below formula:

`=SUMIFS({100;200;120;150;130;230},{"Day1 of Y2020";"Day1 of Y2021";"Day2 of Y2020";"Day2 of Y2021";"Day3 of Y2020";"Day3 of Y2021"},"*2021")`

There is one pair of criteria range and criteria:

Criteria Range 1: {“Day1 of Y2020″;”Day1 of Y2021″;”Day2 of Y2020″;”Day2 of Y2021″;”Day3 of Y2020″;”Day3 of Y2021”}

Criteria 1: “*2021”

In criteria range 1, compare each value in the array with criterion “*2021”; if date is ended with 2021, mark them in bold:

{“Day1 of Y2020″;”Day1 of Y2021“;”Day2 of Y2020″;”Day2 of Y2021“;”Day3 of Y2020″;”Day3 of Y2021“}

So, for dates which matches the criteria, record a ‘True’ in the array; otherwise, record a ‘False’. Then we can get a new array:

{False;True;False;True;False;True}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

{0;1;0;1;0;1}

Now, we have below two arrays:

Sum range: {100;200;120;150;130;230}

Criteria: {0;1;0;1;0;1} – from above steps we know that if date ends with “2021”, 1 is displayed.

We list the two arrays in two rows, and multiply the two numbers in the same column, and save their product in “Product” row, then sum all values in “Product”.

Refer to above table, we finally get 580 as returned value.

In this case, asterisk (*) represents one or more characters, if “*” is omit in criteria, that means cell which is exactly equal to “2021” can satisfy the condition. So, if we enter =SUMIFS(Amount,Date,”2021″) to sum numbers, it doesn’t work.

## FORMULA 2 – APPLY SUMIF FUNCTION

Now, let’s try to apply SUMIF function to resolve this issue. The first two steps are the same.

Step 3: In E3, enter the formula =SUMIF(Date,”*2021″,Amount).

Note: We can see in SUMIF formula, we use the same parameters compare with SUMIFS, the difference is range and criteria are moved to the first two positions.

Step 4: Press Enter after typing the formula. Verify that we get the same result.

## SUMIF FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF, it sums numbers based on criteria. it allows user provides one pair of ‘criteria range’ and ‘criteria’.

For SUMIF function, the syntax is:

SUMIF(range, criteria, [sum_range]) – if sum range=range, then sum range can be omitted.

SUMIF function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

### SUMIF ARGUMENTS EXPLAINATION

SUMIFS and SUMIF share the same parameters in this instance.

SUMIF – RANGE

SUMIF – CRITERIA

SUMIF – SUM RANGE

SUMIF WORKFLOW

SUMIFS and SUMIF have the same criteria range, criteria and sum range, they are processed with the same steps.

SUMMARY

1. SUMIFS function can handle multiple pairs of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. SUMIF function can handle one pair of criteria range and criteria. Sum range is the last argument among all arguments.

3. They allow user defined range name, wildcards, logical operators.

### Related Functions

• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

## How to Sum if Less Than A Number in Excel

Sometimes we may meet the case that to sum numbers with the pre-condition “less than X”. We only want to sum numbers which are less than a supplied number. In this article, we will show you the method to resolve this problem by formulas with the help of Excel SUMIF and SUMIFS functions.

Through a simple instance below, we will introduce you the syntax, argument and the usage of SUMIF and SUMIFS functions. We will let you know how the formula works step by step to reach your goal clearly. After reading the article, you may have a simple understanding of SUMIF/SUMIFS functions.

EXAMPLE

Refer to the left-hand table, we can see that there are two columns “Score” and “Count”. For each score, its adjacent cell records the number “how many students get this score”.  In the right-hand table, it is a simple summary table. There is only one condition that “score: less than 60”, and our expectation is counting the total number of students whose score is less than 60. In this case, user can enter any value into E2, it is a dynamic value. In the formula, we just use cell reference E2 to represent the value, so, if E2 value is changed, the returned value is also changed accordingly.

As we want to sum numbers from “Count” column based on the criteria “Score < 60 (value in E2)”. We need to find out all scores that meet our requirement, and then sum up values in “Count” column accordingly. To resolve this issue by formula, we can apply Excel SUMIF or SUMIFS functions.

FORMULA 1 – APPLY SUMIF FUNCTION

Step 1: Select A2:A9, then in Name Box define a name for this range, just use the column header ‘Score’.

Step 2: Select B2:B9, in Name Box define a name for this range, for example ‘Count’.

Note: Above two steps are not required, they are optional, you can directly use cell or range reference like A2:A9, B2:B9 in your formula, but for understanding well, we name ranges in advance.

Step 3: In E3, enter the formula =SUMIF(Score,”<“&E2,Count).

Note: In step#1 and step#2 we already name ranges “Score” and “Count”, so when entering the range information into formula, just after typing “Sc…”, our named range “Score” is loaded, just select it from dropdown list. As we explained in step#2, you can also hold and drag A2:A9 to fill the argument as well.

Step 4: Press Enter after typing the formula.

In column A, cell A5 (50), A7 (55) are less than 60, the corresponding counts are 5 (in cell B5), 3 (in cell B7), so the total count is 5+3=8. The formula works correctly.

SUMIF FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF, it sums numbers based on criteria. it allows user provides one pair of ‘criteria range’ and ‘criteria’.

For SUMIF function, the syntax is:

SUMIF(range, criteria, [sum_range]) – if sum range=range, then sum range can be omitted.

SUMIF function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

SUMIF ARGUMENTS EXPLAINATION

SUMIF – RANGE

We need to compare each score in “Score” column with 60, so “A2:A9” is the ‘range’.

In the formula bar, select “Score”, press F9, all values in “Score” are listed in an array.

SUMIF – CRITERIA

We enter “<”&E2 as “criteria”. If logical operator is used, it should be quote within double quotes “”; In E2, we enter “60” to filter scores; to concentrate logical operator “<” and number “60”, we need to use a special character “&” to connect them, if “&” is missing, formula quits typing directly.

In the formula bar, select the completed criteria, press F9.

SUMIF – SUM RANGE

In this instance, we need to count numbers of student whose score is less than 60. So, cell reference “B2:B9” in “Count” column is the ‘sum range’.

In the formula bar, select “Count”, press F9, all values in “Count” are listed in an array.

SUMIF WORKFLOW

After explaining each argument in the formula, now we will show you how the formula works with the help of these arguments.

=SUMIF({60;65;70;50;75;55;80;85},”<60″,{2;5;6;5;2;3;4;2})

We have a pair of range and criteria:

RANGE: {60;65;70;50;75;55;80;85}

CRITERIA: “<60”

Compare each number in the array with 60; if number satisfies the condition “<60”, mark it in bold:

{60;65;70;50;75;55;80;85} – In bold if it is <60

As bold number meets our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others.

{Flase;False;False;True;False;True;False;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’ to make this array can be taken into calculation in next step.

{0;0;0;1;0;1;0;0}

Now, we have below two arrays:

{2;5;6;5;2;3;4;2} – Sum Range

{0;0;0;1;0;1;0;0} – from above steps we know that if number is less than 70, 1 is displayed in this number’s position

We list the two arrays in two rows, in the same column multiply the two numbers, and save their product in another row.

Sum up all numbers in the third row. You can use SUM function here and select all values in the third row as reference.

5+3=8

NOTE

If there is no “Count” column, and only “Score” column exists, and you want to sum numbers with the condition “number is less than 60”, you can also use SUMIF function here. In this situation, the sum range and criteria range are the same as there is only one range reference A2:A9, so, the last argument ‘sum range’ can be omitted in the formula.

FORMULA 2 – APPLY SUMIFS FUNCTION

Now, let’s try to apply SUMIFS function to resolve this issue. The first two steps are the same.

Step 3: In E3, enter the formula =SUMIFS(Count,Score,”<“&E2).

Note: We can see in SUMIFS formula, we use the same parameters compare with SUMIF, the difference is “Count” is moved to the first position.

Step 4: Press Enter after typing the formula.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it allows user provides multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

SUMIFS function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

SUMIFS ARGUMENTS EXPLAINATION

SUMIFS and SUMIF share the same parameters in this instance.

SUMIFS – SUM RANGE

SUMIFS – CRITERIA RANGE

SUMIFS – CRITERIA

SUMIFS WORKFLOW

SUMIFS and SUMIF have the same criteria range, criteria and sum range, they are processed with the same steps exactly.

NOTE

Unlike SUMIF function, if sum range and criteria range are the same in a formula, sum range is still required.

Warning message pops up if missing argument.

SUMMARY

1. SUMIFS function can handle multiple pairs of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. SUMIF function can handle one pair of criteria range and criteria. Sum range is the last argument among all arguments.

3. They allow user defined range name, wildcards, logical operators.

### Related Functions

• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

## How to Sum if Greater Than A Number in Excel

Sometimes we may meet the case that to sum numbers with the pre-condition “greater than X” exists. We only want to sum numbers which are greater than a supplied number. In this article, we will show you the method to resolve this problem by formulas with the help of Excel SUMIF and SUMIFS functions.

Through a simple instance below, we will introduce you the syntax, argument and the usage of SUMIF and SUMIFS functions. We will let you know how the formula works step by step to reach your goal clearly. After reading the article, you may have a simple understanding of SUMIF/SUMIFS functions.

## EXAMPLE

Refer to the left-hand table, we can see that there are two columns “Score” and “Count”. For each score, its adjacent cell records the number of “how many students get this score in an exam”.  In the right-hand table, it is a simple summary, there is one criterion that “score is greater than 70”, and the expectation is counting the total number of students whose score is greater than 70. Actually 70 is what we just entered into E2, we can also change it to another value.

In this instance, we want to sum numbers from “Count” column based on the criteria “Score > 70 (value in E2)”. We need to find out all scores that meet our requirement, and then sum up values in “Count” column accordingly. To resolve this issue by formula, we can apply Excel SUMIF or SUMIFS functions.

## FORMULA 1 – APPLY SUMIF FUNCTION

Step 1: Select A2:A9, then in Name Box define a name for this range, just use the column header ‘Score’.

Step 2: Select B2:B9, in Name Box define a name for this range, for example ‘Count’.

Note: Above two steps are not required, they are optional, you can directly use cell or range reference like A2:A9, B2:B9 in your formula, but for understanding well, we name ranges in advance.

Step 3: In E3, enter the formula =SUMIF(Score,”>”&E2,Count).

Note: In step#1 and step#2 we already name ranges “Score” and “Count”, so when entering the range information into formula, just after typing “Sc…”, our named range “Score” is loaded, just select it from dropdown list. As we explained in step#2, you can also hold and drag A2:A9 to fill the argument as well.

Step 4: Press Enter after typing the formula.

In column A, cell A6 (75), A8 (80) and A9 (85) are greater than 70, the corresponding counts are 2 (in cell B6), 4 (in cell B8), and 2 (in cell B9), so the total count is 2+4+2=8. The formula works correctly.

## SUMIF FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF, it sums numbers based on criteria. it allows user provides one pair of ‘criteria range’ and ‘criteria’.

For SUMIF function, the syntax is:

SUMIF(range, criteria, [sum_range]) – if sum range=range, then sum range can be omitted.

SUMIF function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

### SUMIF ARGUMENTS EXPLAINATION

#### SUMIF – RANGE

In this instance, we need to count numbers of student whose score is greater than 70. We need to compare each score in “Score” column with 70, so “A2:A9” is the ‘range’.

In the formula bar, select “Score”, press F9, all values in “Score” are listed in an array.

#### SUMIF – CRITERIA

We enter “>”&E2 into criteria part. As we mention above, if logical operator is used, it should be quote with double quotes “”; In E2 we supply a certain value “70” to filter scores; to concentrate logical operator “>” and number “70”, we must to use a special character “&” to connect them, if “&” is missing, formula quits directly.

In the formula bar, select the complete criteria, press F9.

#### SUMIF – SUM RANGE

In this instance, we need to count numbers of student whose score is greater than 70. So “Count” column range “B2:B9” is the ‘sum range’.

In the formula bar, select “Count”, press F9, all values in “Count” are listed in an array.

#### SUMIF WORKFLOW

After explaining each argument in the formula, now we will show you how the formula works with the help of these arguments.

=SUMIF({60;65;70;50;75;55;80;85},”>70″,{2;5;6;5;2;3;4;2})

We have a pair of range and criteria:

RANGE: {60;65;70;50;75;55;80;85}

CRITERIA: “>70”

Compare each number in array with 70; if number can satisfy the condition “>70”, mark it bold in array:

{60;65;70;50;75;55;80;85} – Bold if it is >70

Now, for bold numbers, as they can meet our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others.

{Flase;False;False;False;True;False;True;True}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’ to make this array can be taken into calculation in next step.

{0;0;0;0;1;0;1;1}

Now, we have below two arrays:

{2;5;6;5;2;3;4;2} – Sum Range

{0;0;0;0;1;0;1;1} – from above steps we know that if number is greater than 70, 1 is displayed in this number’s position

We list the two arrays in two rows, in the same column multiply the two numbers, and save their product in another row.

Sum up all numbers in the third row. You can use SUM function here and select all values in the third row as reference.

2+4+2=8

## FORMULA 2 – APPLY SUMIFS FUNCTION

Now, let’s try to apply SUMIFS function to resolve this issue. The first two steps are the same.

Step 1: In E3, enter the formula =SUMIFS(Count,Score,”>”&E2).

Note: We can see in SUMIFS formula, we use the same parameters compare with SUMIF, the difference is “Count” is moved to the first position.

Step 2: Press Enter after typing the formula.

### SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it allows user provides multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …).

SUMIFS function allows wildcards like asterisk ‘*’ and question mark ‘?’, it also allows logical operators within its argument. If wildcards or logical operators are required, they should be enclosed into double quotes (““).

### SUMIFS ARGUMENTS EXPLAINATION

SUMIFS and SUMIF share the same parameters in this instance.

SUMIFS – SUM RANGE

SUMIFS – CRITERIA RANGE

SUMIFS – CRITERIA

SUMIFS WORKFLOW

SUMIFS and SUMIF have the same criteria range, criteria and sum range, they are processed with the same steps exactly.

SUMMARY

1. SUMIFS function can handle multiple pairs of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. SUMIF function can handle one pair of criteria range and criteria. Sum range is the last argument among all arguments.

3. They allow user defined range name, wildcards, logical operators.

### Related Functions

• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

## How to Sum if Date is Greater Than A Date in Excel

In previous article, we have introduced you the method to sum if date is included in a period, you can check “How to Sum if Date Between Two Dates” on our website for reference. But sometimes we may want to sum numbers not for a period but just after a date. In this article, we will show you how to ‘sum if date is greater than a date’ by formula with the help of Excel SUMIF/SUMIFS function. We also introduce DATE function in case of date format is not a valid date format so that it cannot be used in a formula directly.

Through a simple instance, we will introduce you the syntax, arguments of SUMIF/SUMIFS function, and let you know how the formula works step by step to reach your goal finally. After reading the article, you may have a simple understanding of SUMIF/SUMIFS/DATE functions.

EXAMPLE

Refer to above left-hand table, we can see that in the most left column “Date”, some dates are listed. And amount for each date is listed in “Amount” column accordingly.

In this instance, we want to sum numbers after a certain date. And this date is saved in “After Date” field in the right-hand table. This date is a dynamic date and we can change it properly based on our requirement. After the date is confirmed, only the dates which are later than the date can meet our condition. We need to find out all proper dates, and then sum up values in “Amount” column. To resolve this issue by formula, we can apply SUMIF or SUMIFS.

FORMULA – SUMIF FUNCTION

Step 1: Select A2:A9, then in Name Box define a new name for this range, for example ‘Date’.

Step 2: Select B2:B9, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In E2, enter the formula =SUMIF(Date,”>”&DATE(2021,4,15),Amount).

NOTE: In step#1 and step#2 we defined range name “Date” and “Amount”, when entering the formula, after typing “Amou…”, defined range “Amount” is auto loaded, you can directly select it from dropdown list. You can also select B2:B9, A2:A9 to fill formula arguments as well. Be aware that don’t select DATE function in the dropdown list.

Step 4: Press Enter after typing the formula.

We can see in column A, cell A7 (4/18/2021), A8 (5/5/2021) and A9 (6/6/2021) are after date “4/15/2021”, so the corresponding amounts are 230 (in cell B7), 150 (in cell B8), and 250 (in cell B9), so the total amount is 230+150+250=630. The formula works correctly.

FORMULA – SUMIFS FUNCTION

We can also apply SUMIFS function to resolve this issue.

Step 1: In E2, enter the formula =SUMIFS(Amount,Date,”>”&DATE(2021,4,15)).

Step 2: Press Enter after typing the formula.

We can see that the difference between SUMIF and SUMIFS is argument order. Compare with SUMIF, SUMIFS function list sum range at first, then criteria range, and criteria lists in the end. Besides, SUMIFS function allows multiple “criteria range and criteria” combinations.

FUNCTION INTRODUCTION

1. SUMIF Function

SUMIFS function can be seen as SUM+IF, it can sum based on criteria.

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range]) – Argument in [] is optional. If sum range is omitted, SUMIF will sum up all numbers in range argument.

2. SUMIFS Function

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

Both SUMIF and SUMIFS function allow wildcards like asterisk ‘*’ and question mark ‘?’, they also allow logical operators within arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part. For example, “*A*” means characters or texts are both listed before and after “A”. “A*” means this text is started with A, but ends with others.

The usage of logical operators:

1. “>” – greater than
2. “>=” – greater than or equal to
3. “<” – less than
4. “<” – less than or equal to
5. “<>” – not equal to
6. To connect logical operator and text, we need to add an ampersand “&” to concentrate them. For example “>” & 100 or “<=” & E2.
7. DATE Function

DATE function can return a serial number that can represent a particular date if current cell format is ‘General’ or directly return a date if current cell format is ‘Date’. The syntax is

DATE(year,month,day) – All the three arguments ‘year’,’month’,’day’ are required.

ARGUMENTS

Let’s introduce the arguments of used functions.

DATE – YEAR, MONTH, DAY

If date needs to be hard coded in a formula, we often use DATE function. In this case, date is 15-Apr-2021, so year=2021, month=4, day=15.

In the formula bar, select ‘”>”&Date(2021,4,15)’, press F9, date is converted to a five digits number in the formula.

In this case, SUMIF and SUMIFS have the same sum range, criteria range and criteria references, and working processes are similar, so we only clarify SUMIFS function in the following parts.

SUMIFS – SUM RANGE

In our instance, B2:B9 is the ‘sum range’. Amount values are listed in this field. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE

A2:A9 is the criteria range. In this instance we have only one criteria range. This range contains different dates.

In the formula bar, select ‘Date’, press F9, values in this range are listed in an array.

In this step, dates are converted from Date format to General format, a five digits number is displayed to represent a date. Date 12/30/2020 in A2 is the earliest date in the list, so its relative five digits number 44195 is the smallest in the array, and date 6/6/2021 is the latest date in the list, so 44353 is the largest among all numbers in the array.

SUMIFS – CRITERIA

We want to calculate total amount after date “4/15/2021”. As in the table ‘’ is not a valid date, so we cannot directly use cell reference in the formula, so DATE function is applied here. As the target date should be after “4/15/2021”, so “>” is added. The complete criteria is “>”&DATE(2021,4,15).

HOW FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, the formula is converted into below format in the formula bar.

=SUMIFS({100;120;200;150;130;230;150;250},{44195;44227;44228;44252;44298;44304;44321;44353},”>44301″)

We have below pair of criteria range and criteria:

Criteria range: {44195;44227;44228;44252;44298;44304;44321;44353}

Criteria: “>44301”

In criteria range, compare each number in the array with criteria 44301; if number can satisfy the condition “>44301”, mark it bold in array:

{44195;44227;44228;44252;44298;44304;44321;44353} – Bold if it is >44301

Now, for bold numbers, they can meet our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others. Then we can get a new array:

{Flase;False;False;False;False;True;True;True}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’ to apply this array into calculation in the following steps:

{0;0;0;0;0;1;1;1}

Now, we have below two arrays:

{100;120;200;150;130;230;150;250} – Sum Range

{0;0;0;0;0;1;1;1} – from above steps we know that if date is after “4/15/2021”, 1 is displayed.

We list the two arrays in two rows, in the same column multiply the two numbers, and save their product in another row.

Sum up all numbers in the third row.

230+150+250=630

COMMENT

1.If “After Date” value is changed in E2, we need to update DATE function year, month, date values manually.

2.If date is a valid date like 4/15/2021, we can directly use cell reference in the formula. See example below.

SUMMARY

1. SUMIFS function can handle multiple pairs of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. SUMIF function can handle one pair of criteria range and criteria. Sum range is the last argument among all arguments.

3. They allow user defined range name, wildcards, logical operators.

If you want to hard-code date in formula, you can apply DATE function.

### Related Functions

• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel DATE function
The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…
• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…

## How to Sum if Date Between Two Dates in Excel

In previous article, we have introduced you the method to sum if by year, you can check “How to Sum Data if by Year in Excel” on our website for reference. But sometimes we may want to sum numbers not for a year but just for a period. In this article, we will show you how to ‘sum if date between two dates’ by formula with the help of Excel SUMIFS function. Due to date format issue may occurs in real life, we also introduce DATE function in case of you meet the same problem as we describe in example2.

Through a simple instance, we will introduce you the syntax, arguments of SUMIFS function, and let you know how the formula works step by step to reach your goal finally. After reading the article, you may have a simple understanding of SUMIFS function.

EXAMPLE

Refer to above left-hand table, we can see that in the most left column “Date”, some dates are listed. And amount for each date is listed in “Amount” column accordingly.

In this instance, we want to sum numbers between a period. And this period is confirmed by the two dates “Start Date” and “End Date” in the right-hand table. This period is a dynamic period due to we can change “Start Date” and “End Date” based on our requirement. After the period is provided, only the dates which are between this period can meet our condition. We need to find out all proper dates, and then sum up values in “Amount” column. To resolve this issue by formula, we can apply SUMIFS functions.

FORMULA – SUMIFS FUNCTION

Step 1: Select A2:A9, then in Name Box define a new name for this range, for example ‘Date’.

Step 2: Select B2:B9, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In E4, enter the formula =SUMIFS(Amount,Date,”>”&E2,Date,”<” & E3).

NOTE: In step#1 and step#2 we defined range name “Date” and “Amount”, when entering the formula, after typing “Amou…”, defined range “Amount” is auto loaded, you can directly select it from dropdown list. You can also select B2:B9, A2:A9 to fill formula arguments as well.

Step 4: Press Enter after typing the formula.

We can see in column A, cell A5 (2/25/2021) and A6 (4/12/2021) just between period “2/25/2021-4/15/2021”, the corresponding amounts are 150 (in cell B5), 130 (in cell B6), and 150 (in cell B8), so the total amount is 150+130=280. The formula works correctly.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

SUMIFS function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part. For example, “*A*” means characters or texts are both listed before and after “A”. “A*” means this text is started with A, but ends with others.

The usage of logical operators:

1. “>” – greater than
2. “>=” – greater than or equal to
3. “<” – less than
4. “<” – less than or equal to
5. “<>” – not equal to

ALL ARGUMENTS

SUMIFS – SUM RANGE

In our instance, B2:B9 is the ‘sum range’ obviously. Amount values are listed in this field. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE 1

A2:A9 is the criteria range. In this instance we have only one criteria range. This range contains different dates.

In the formula bar, select ‘Date’, press F9, values in this range are listed in an array.

In this step, dates are converted from Date format to General format, a five digits number is displayed to represent a date. Date 12/30/2020 in A2 is the earliest date in the list, so its relative five digits number 44195 is the smallest in the array, and date 6/6/2021 is the latest date in the list, so 44353 is the largest among all numbers in the array.

SUMIFS – CRITERIA 1

We want to calculate total amount for the period between start date “2/15/2021” and end date “4/15/2021”. The two dates are saved in E2 and E3, we can directly enter cell reference in the formula to stand for the two dates. As the target date should be included in the two dates, that means it is greater than the start date (>E2) and at the mean time it is also smaller than the end date (<E3).

Thus, the first criteria should be “greater than the date in E2”. As we explained above, SUMIFS function allows logical operators, but they should be enclosed into double quotes “”, so for criteria 1, we enter logical operator > within quotes as “>”. And to concentrate logical operator and cell reference E2, we add & between them. Please be aware that if you enter “>”E2 without & into formula, formula cannot accept this combination and directly quit entering. After all, for criteria 1, we enter “>”&E2.

E2=2/15/2021, convert it to general format in the formula bar, it is 44242.

SUMIFS – CRITERIA RANGE 2

It is still range A2:A9.

SUMIFS – CRITERIA 2

The second criteria should be “smaller than the date in E3”. So we enter “<”&E3 in the formula.

E3=4/15/2021, convert it to general format in the formula bar, it is 44301.

HOW FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, the formula is converted into below format in the formula bar.

=SUMIFS({100;120;200;150;130;230;150;250},{44195;44227;44228;44252;44298;44304;44321;44353},”>”&44242,{44195;44227;44228;44252;44298;44304;44321;44353},”<“&44301)

See screenshot below:

See the two pairs of criteria range and criteria:

Criteria range 1: {44195;44227;44228;44252;44298;44304;44321;44353}

Criteria 1: “>”&44242

In criteria range 1, compare each number in the array with criteria 44242; if number can satisfy the condition “>44242”, mark it bold in array:

{44195;44227;44228;44252;44298;44304;44321;44353} – Bold if it is >44242

Criteria range 2: {44195;44227;44228;44252;44298;44304;44321;44353}

Criteria 2: “<“&44301

In criteria range 2, compare each number in the array with criteria 44301; if number can satisfy the condition “<44301”, mark it bold in array:

{44195;44227;44228;44252;44298;44304;44321;44353} – Bold if it is <44301

Find out the intersection between two arrays. The result can meet our requirement “between 44242 and 44301” which means “between the period 2/15/2021 and 4/15/2021”.

{44195;44227;44228;44252;44298;44304;44321;44353} – Bold if it is just between 44242 and 44301.

Now, for bold numbers, they can meet our requirement, so record a ‘True’ for them in the array; otherwise, record a ‘False’ for others. Then we can get a new array:

{Flase;False;False;True;True;False;False;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’ to apply this array into calculation in the following steps:

{0;0;0;1;1;0;0;0}

Now, we have below two arrays:

{100;120;200;150;130;230;150;250} – Sum Range

{0;0;0;1;1;0;0;0} – from above steps we know that if date is included in the period, 1 is displayed.

We list the two arrays in two rows, in the same column multiply the two numbers, and save their product in another row.

Sum up all numbers in the third row.

150+130=280

EXAMPLE 2

Sometimes dates are not just displayed in proper date format which can be directly applied in formula as we expect. They can be displayed in some other date formats in worksheet, see example below:

In this situation, if you want to sum numbers based on period, you should hard code dates by DATE function, then refer to above method to apply SUMIFS function.

FORMULA – SUMIFS AND DATE FUNCTIONS

Step 1: In E4, enter the formula =SUMIFS(Amount,Date,”>”&DATE(2021,2,15),Date,”<“&DATE(2021,4,15)).

Step 2: Press Enter after typing the formula.

DATE FUNCTION INTRODUCTION

DATE function can return a serial number that can represent a particular date if current cell format is ‘General’ or directly return a date if current cell format is ‘Date’. The syntax is

DATE(year,month,day) – All the three arguments ‘year’,’month’,’day’ are required.

HOW FORMULA WORKS

We applied DATE function twice in this case, the start date is 15-Feb-2021, end date is 15-Apr-2021, so in the first DATE function we enter DATE(2021,2,15), and the second DATE function we enter DATE(2021,4,15).

In the formula bar, select “DATE(2021,2,15)”, press F9, date is converted to a five digits number 44242.

Then select “DATE(2021,2,15)”, press F9, date is converted to a five digits number 44301.

Now, we get the two numbers, the following steps are as same as example1.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators.

If you want to hard-code date in formula, you can apply DATE function.

### Related Functions

• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…
• Excel DATE function
The Excel DATE function returns the serial number for a date.The syntax of the DATE function is as below:= DATE (year, month, day)…

## How to Sum by Formula if Cell Contains Special Character in Excel

In previous article, we have introduced you the method to sum if cell is equal to a certain value, you can check “How to Sum Numbers by Formula if Cells Equal to A Certain Value” on our website for reference. By the way, if special characters exist in the target, can we use the same method to sum numbers? In this article, we will show you how to ‘sum if cell contains special character’ by formula with the help of Excel SUMIFS function.

Through demonstrate a simple instance, we will introduce you the syntax, arguments of SUMIFS function, and let you know how the formula works step by step and finally reach your goal. After reading the article, you may have a simple understanding of SUMIFS function. We will also introduce you another method to resolve this issue by SUMIF function at the end.

EXAMPLE

Refer to above table, we can see that some different types of products are listed in “Product” column. Product serial number consists of three parts, and the three parts are connected with special character “-”. Amount for each type product is listed in “Amount” column accordingly.

In this instance, we want to calculate total amount for the product which serial number is combined with “C1-V1-xxxx”. So, only the products which the first two parts are “C1-V1” can meet the condition. We need to find out all proper products, and then sum up values in “Amount” column. To resolve this issue by formula, we can apply SUMIFS or SUMIF functions.

FORMULA – SUMIFS FUNCTION

Step 1: Select A2:A9, then in Name Box define a new name for this range, for example ‘Product’.

Step 2: Select B2:B9, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In E2, enter the formula =SUMIFS(Amount,Product,”C1-V1-*”).

NOTE: In step#1 and step#2 we defined range name “Product” and “Amount”, when entering the formula, after typing “Amou…”, defined range “Amount” is auto loaded, you can directly select it from dropdown list. You can also select B2:B9, A2:A9 to fill formula arguments as well.

Step 4: Press Enter after typing the formula.

We can see in column A, cell A2, A4 and A8 contain “C1-V1”, the corresponding amounts are 100 (in cell B2), 200 (in cell B4), and 150 (in cell B8), so the total is 100+200+150=450. The formula works correctly.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

SUMIFS function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part. For example, “*A*” means characters or texts are both listed before and after “A”. “A*” means this text is started with A, but ends with others.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

ALL ARGUMENTS

SUMIFS – SUM RANGE

In our instance, B2:B9 is the ‘sum range’ obviously. Amount values are listed in this field. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE 1

A2:A9 is the criteria range. In this instance we have only one criteria range. This range contains different types of products.

In the formula bar, select ‘Product’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA 1

We want to calculate total amount for the products contain “C1-V1-”. In this criterion a special character “-” exists, as we mentioned above, SUMIFS function supports texts and special character, so just enter “C1-V1-” to fill the criteria. As cells are not ended with “C1-V1-”, the completed combination is “C1-V1-XXXX”, so a “*” is added after “C1-V1-” to represent “XXXX” part. After all, for criteria 1, we enter “C1-V1-*” to fill this argument.

HOW FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, the formula is converted into below format in the formula bar.

=SUMIFS({100;120;200;150;130;230;150;250},{“C1-V1-0001″;”C1-V2-0001″;”C1-V1-0002″;”B1-P1-0001″;”B1-P1-0002″;”B1-P1-0003″;”C1-V1-0003″;”C1-V2-0002″},”C1-V1-*”)See screenshot below:

See the pair of criteria range and criteria:

Criteria Range 1: {“C1-V1-0001″;”C1-V2-0001″;”C1-V1-0002″;”B1-P1-0001″;”B1-P1-0002″;”B1-P1-0003″;”C1-V1-0003″;”C1-V2-0002”}

Criteria 1: “C1-V1-*”

In criteria range 1, compare each product serial number in the array with “C1-V1-*”; if “C1-V1-” exists in product serial number, mark them in bold:

{“C1-V1-0001“;”C1-V2-0001″;”C1-V1-0002“;”B1-P1-0001″;”B1-P1-0002″;”B1-P1-0003″;”C1-V1-0003“;”C1-V2-0002”}

So, for bold texts, record a ‘True’ in the array; otherwise, record a ‘False’. Then we can get a new array:

Array 1: {True;False;True;False;False;False;True;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

Array 1: {1;0;1;0;0;0;1;0}

Now, we have below two arrays:

Sum Range: {100;120;200;150;130;230;150;250}

Array 1: {1;0;1;0;0;0;1;0} – from above steps we know that if cell contains “C1-V1-”, 1 is displayed.

We list the two arrays in two rows, in the same column multiply the two numbers, and save their product in another row.

Sum up all numbers in the third row.

100+200+150=450

In this case, we can also use SUMIF function to sum cell properly.

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range]). Argument in [] is optional. If sum range is omitted, SUMIF will sum up all numbers in range argument.

In E2 enter the formula =SUMIF(Product,”C1-V1-*”,Amount).

After entering the formula, we can see that SUMIF function also works correctly. Actually, in most situations, SUMIF function can be used instead of SUMIFS function, the difference is SUMIF function can only support one pair of criteria range and criteria. So, if only one criterion is supplied to filter data, you can select either SUMIF or SUMIFS you like to sum cells.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators.

### Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

## How to Sum by Formula if Cell Contains Both A and B in Excel

In previous articles, we have introduced the method of sum numbers for cells which are equal to a certain value or are not equal to a certain value (you can refer to ‘How to Sum if Cell Contains Text in Another Column’, ‘How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value’ on our website). In this article, we will show you the how to ‘sum if cell contains both A and B’ by formula with the help of Excel SUMIFS function.

Through demonstrate a simple instance, we will introduce you the syntax, arguments of SUMIFS function, and let you know how this formula works to reach your goal. After reading the article, you may have a simple understanding of SUMIFS function.

EXAMPLE

Refer to above table, we can see that some kinds of fruit are listed in “Fruit” column. At least two fruits are displayed in one cell. Total amount for fruit combination is listed in “Amount” column accordingly.

In this instance, we want to calculate total amount for fruit combination that contains “Apple” and “Orange”. So, no matter “Apple, Orange” or “Apple, Orange, Grape”, they are both seem to match the criterion. We need to find out all proper fruit combination, and then sum up values in “Amount” column. To fix this issue by formula, we can apply SUMIFS function here.

FORMULA – SUMIF FUNCTION

Step 1: Select A2:A9, then in Name Box define a new name for this range, for example ‘Fruit’.

Step 2: Select B2:B9, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In E2, enter the formula =SUMIFS(Amount,Fruit,”*Apple*”,Fruit,”*Orange*”).

Step 4: Press Enter after typing the formula.

We can see in column A, cell A2, A4 and A8 contains both “Apple” and “Orange”, the corresponding amounts are 100 (in cell B2), 200 (in cell B4), and 150 (in cell B8), so the total is 100+200+150=450. The formula works correctly.

SUMIFS FUNCTION INTRODUCTION

SUMIFS function can be seen as SUM+IFS, it can handle multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, the syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …). Contents in [] are optional.

SUMIFS function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

ALL ARGUMENTS

SUMIFS – SUM RANGE

In our instance, B2:B9 is the ‘sum range’ obviously. Amount values are listed in this field. We define this range with name ‘Amount’ in above step#2.

In the formula bar, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE 1

A2:A9 is the criteria range. In this instance we have only one criteria range. This range contains several kinds of fruit.

In the formula bar, these fruits are displayed in proper order in an array:

SUMIFS – CRITERIA 1

We want to calculate total amount for combinations contain “Apple” and “Orange”, so we will supply two criteria in this case, the first one is “Apple”. As there might be some characters listing before or after “Apple”, so we need to add “*” before and after “Apple”. As SUMIFS function supports texts and wildcards, but they should be enclosed into double quotes “ “ when applying them, so for criteria 1, we enter “*Apple*” in this position.

SUMIFS – CRITERIA RANGE 2

We have only one criteria range. So in criteria range2 position, we still enter “Amount”.

SUMIFS – CRITERIA 2

In criteria 1 we enter “*Amount*”, so in criteria 2 we enter “*Orange*”. Then the two criteria are both included in this formula.

HOW FORMULA WORKS

After explaining each argument in the formula, now we will show you how the formula works with these arguments.

Refer to above mentioned arguments, we can get three arrays and two texts within double quotes, the formula is converted into below format in the formula bar.

=SUMIFS({100;120;200;150;130;230;150;250},{“Apple, Orange, Grape”;”Banana, Orange”;”Orange, Apple”;”Apple, Banana”;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange”;”Banana, Grape”},”*Apple*”,{“Apple, Orange, Grape”;”Banana, Orange”;”Orange, Apple”;”Apple, Banana”;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange”;”Banana, Grape”},”*Orange*”)

See screenshot below:

There are two pairs of criteria range and criteria:

Criteria Range 1: {“Apple, Orange, Grape”;”Banana, Orange”;”Orange, Apple”;”Apple, Banana”;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange”;”Banana, Grape”}

Criteria 1: “*Apple*”

Criteria Range 2: {“Apple, Orange, Grape”;”Banana, Orange”;”Orange, Apple”;”Apple, Banana”;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange”;”Banana, Grape”}

Criteria 2: “*Orange*”

In criteria range 1, compare each fruit combination in the array with criterion “Apple”; if “Apple” exists in fruit combination, mark them in bold:

{“Apple, Orange, Grape“;”Banana, Orange”;”Orange, Apple“;”Apple, Banana“;”Banana, Orange, Grape”;”Orange, Banana”;”Apple, Grape, Orange“;”Banana, Grape”}

So, for the combination which meets the criteria, record a ‘True’ in the array; otherwise, record a ‘False’. Then we can get a new array:

Array 1: {True;False;True;True;False;False;True;False}

In criteria range 2, compare each fruit combination in the array with criterion “Orange”; if “Orange” exists in fruit combination, mark them in bold:

{“Apple, Orange, Grape“;”Banana, Orange“;”Orange, Apple“;”Apple, Banana”;”Banana, Orange, Grape“;”Orange, Banana“;”Apple, Grape, Orange“;”Banana, Grape”}

Then we get another array:

Array 2: {True;True;True;False;True;True;True;False}

As the two criteria “Apple” and “Orange” both should be included in the fruit combination, so we need to look up the intersection of array1 and array2, only True is marked in the same position in two arrays, True will keep in array. So, after comparing the two arrays, we get a new array3:

Array 3: {True;False;True;False;False;False;True;False}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

Array 4: {1;0;1;0;0;0;1;0}

Now, we have below two arrays:

Sum range: {100;120;200;150;130;230;150;250}

Array 4: {1;0;1;0;0;0;1;0} – from above steps we know that if cell contains both “Apple” and “Orange”, 1 is displayed.

We list the two arrays in two rows, and multiply the two numbers in the same column, and save their product in sum total.

Above all, numbers which are not zero in sum total row are the amounts of cell contains both ‘Apple’ and ‘Orange’. Now, sum up all numbers in sum total.

100+200+150=450

In this case, if we forgot to add “*” in criteria, let’s see what do we get.

In fact, an asterisk (*) means one or more characters, if * is omit in criteria, that means cell which is exactly equal to the criteria can satisfy the condition. As we all know, a cell cannot either be ‘Apple’ or ‘Orange’. So, if we enter =SUMIFS(Amount,Fruit,”Apple”,Fruit,”Orange”) to sum numbers, it doesn’t work.

SUMMARY

1. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

2. It supports user defined range name.

3. It supports wildcard.

4. It supports logical operators.

### Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…

## How to Sum Numbers by Formula if Cells Are Not Equal to Certain Value in Excel

In previous article, we introduce the method to sum numbers for cells are equal to a certain. In this article, we will show you the resolution of ‘sum if cells are not equal to a certain value’ by formula with the help of Excel SUMIF function. Through demonstrate a simple instance, we will introduce you the syntax, arguments of SUMIF function, and let you know how this formula works to reach our goal. Some other functions like SUMIFS can also resolve this issue, we will also introduce SUMIFS function a little bit in the end to let you know another way of sum number with condition. After reading the article, you may have a simple understanding of SUMIF function.

EXAMPLE

Refer to above table, we can see that we sale only one kind of fruit each week. We list week number in column A, some kinds of fruits in column B, fruits are on sale alternatively. Total amount for each week for specific fruit is listed in column C.

In this instance, we want to calculate total amount for fruits except fruit ‘Apple’. That means we need to sum up numbers in column C which B column in the same row is not equal to ‘Apple’. We can apply a formula contains SUMIF function to resolve this issue.

FORMULA – SUMIF FUNCTION

Step 1: Select B2:B9, then in Name Box define a new name for this range, for example ‘Fruit’.

Step 2: Select C2:C9, in Name Box define a new name for this range, for example ‘Amount’.

Step 3: In F2, enter the formula =SUMIF(Fruit,”<>Apple”,Amount).

Step 4: Press Enter after typing the formula.

We can see in column A there are five cells are not equal to text ‘Apple’, the corresponding amounts are 120 (in cell C3), 200 (in cell C4), 130 (in cell C6), 230 (in cell C7) and 250 (in cell C9), so the total is 120+200+130+230+250=930. The formula works correctly.

SUMIF FUNCTION INTRODUCTION

SUMIF function can be seen as SUM+IF, it can handle one combination of ‘criteria range’ and ‘criteria’.

For SUMIF function, the syntax is:

=SUMIF (range, criteria, [sum_range]). Argument in [] is optional. If sum range is omitted, SUMIF will sum up all numbers in range argument.

SUMIF function supports wildcards like asterisk ‘*’ and question mark ‘?’, it also supports logical operators within its arguments. If wildcards or logical operators are required, they should be enclosed into double quotes (““) with text.

The usage of wildcards:

1. An asterisk (*) means one or more characters.
2. A question mark (?) means one character.
3. The position of asterisk or question mark means the character(s) position relative to the entered part.

The usage of logical operators:

1. “>” – greater than
2. “<” – less than
3. “<>” – not equal to

ALL ARGUMENTS

SUMIF – RANGE

In our instance, B2:B9 is the ‘range’ obviously. We define this range with name ‘Fruit’ in precondition.

In the formula bar, select ‘Fruit’, press F9, values in this range are listed in an array.

SUMIF – CRITERIA

We can supply a criterion to check if values in criteria range meet this criterion. As we want to calculate total amount for fruits except ‘Apple’, equivalent to calculate total amount for both Banana and Orange. If we sum numbers based on fruit which is equal to ‘Banana’ or ‘Orange’, ‘Banana’ and ‘Orange’ cannot be both included in formula as we can only supply one criterion. So, in criteria position we enter ‘<>Apple’,’<>’ means ‘is not equal to’. We need to look up all cells which are not equal to ‘Apple’ from criteria range. SUMIF function supports text, but they should also be enclosed into double quotes “ “.

SUMIFS – SUM RANGE

C2:C9 is the sum range. We define this range with name ‘Amount’ in precondition, it is easy to understand than just entering ‘C2:C9’ in sum range position in formula. In the formula bar, amounts are displayed in proper order in an array:

HOW FORMULA WORKS

Below analysis can help us know the process of this formula reaching the goal step by step clearly.

After introducing each argument, we get two arrays and a text within double quotes, the formula is converted into below format in the formula bar.

`=SUMIF({"Apple";"Banana";"Orange";"Apple";"Banana";"Orange";"Apple";"Banana"},"<>Apple",{100;120;200;150;130;230;150;250})`

See screenshot below:

As mentioned above, range and criteria are:

Criteria range: {“Apple”;”Banana”;”Orange”;”Apple”;”Banana”;”Orange”;”Apple”;”Banana”}

Criteria: “<>Apple”

Compare each text in ‘range’ array with supplied criteria ‘is not equal to Apple’. If “Apple” occurs, it doesn’t meet the supplied criteria, so mark a ‘False’ in this position; if text is not ‘Apple’, then mark a ‘True’ in this position.

So, after comparing we get below array:

{False;True;True;False;True;True;False;True}

Convert ‘True’ to ‘1’ and ‘False’ to ‘0’:

{0;1;1;0;1;1;0;1}

Now, we have below two arrays:

Array 1: {100;120;200;150;130;230;150;250} – sum range

Array 2: {0;1;1;0;1;1;0;1} – if cell <> ‘Apple’, 1 is displayed.

We list the two arrays in two rows, and create a new array ‘Array3’ to save numbers based on different values in array2. For example, in array1, value will be copied to array3 only if array2 value in the same column is “1”, otherwise, “0” will be recorded in array3 instead of the original value. So, we get below table finally:

In array2, 1 means in this position the original value is not equal to ‘Apple’; Array1 records amount for each kind of fruit in array2. Above all, numbers which are not zero in array3 are the amounts of ‘Banana’ and ‘Orange’ after filtering. Now, sum up all values in array3.

120+200+130+230+250=930

1. To understand the formula easily, we define range name in precondition. If you ignore this step, you can directly enter the range reference in formula.

2. For this instance, you can also apply SUMIFS function.

Enter =SUMIFS(Amount,Fruit,”<>Apple”) instead of applying SUMIF formula.

We can get the same result based on above formula. You can see that the order of arguments is a little different than SUMIF function.

SUMIFS function can be seen as SUM+IFS, so it can handle multiple criteria range and criteria combinations. As we mentioned above, we want to sum numbers for ‘Banana’ and ‘Orange’, so, can we supply two criteria ‘Banana’ and ‘Orange’ within SUMIFS function in this case? Like =SUMIFS(Amount,Fruit,”Banana”,Fruit,”Orange”) ? In fact, we cannot set ‘Banana’ and ‘Orange’ as two criteria, because in criteria range there is no one fruit can meet both two criteria ‘Banana’ and ‘Orange’. Fruit is either banana or orange, it cannot be both. So, if we enter =SUMIFS(Amount,Fruit,”Banana”,Fruit,”Orange”) to sum numbers, it doesn’t work.

SUMMARY

1. SUMIF function can handle one group of criteria ranges and criteria. Sum range is optional. Sum range is the last argument among all arguments.

2. SUMIFS function can handle multiple groups of criteria ranges and criteria. Sum range is the first argument among all arguments.

3. They both support user defined range name.

4. They both support wildcard.

5. They both support logical operators.

### Related Functions

• Excel SUMIF Function
The Excel SUMIF function sum the numbers in the range of cells that meet a single criteria that you specify. The syntax of the SUMIF function is as below:=SUMIF (range, criteria, [sum_range])…
• Excel SUMIFS Function
The Excel SUMIFS function sum the numbers in the range of cells that meet a single or multiple criteria that you specify. The syntax of the SUMIFS function is as below:=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)…