## 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 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],…)

## How to Calculate Sum of a Column Ignore #N/A in Excel

If you need to calculate the sum of a column in Excel while ignoring #NA errors, this post will guide you through three different methods to accomplish this task. Each method has its own advantages and limitations, so you can choose the one that works best for your specific situation.

Additionally, if you prefer to use VBA code to automate this process, we will provide you with an example that uses the Application.InputBox function to select the range of cells to sum and the destination cell for the result.

## 1. Video: Sum a Column of Cells Ignore #N/A

This video will demonstrate three methods, including formulas and VBA code, for calculating the sum of a column in Excel while ignoring #NA errors.

## 2. Sum a Column of Cells Ignore #N/A Using SUMIF Function

To Sum a column of cells while ignoring #N/A errors in Excel, you can use the SUMIF function. Here’s how you can do it:

Step1: in an empty cell, type the following SUMIF formula:

``=SUMIF(A1:A12,"<>#N/A")``

Step2: press Enter key in your keyboard.

This formula uses the SUMIF function to sum only the cells in the specified range that are not equal to “#N/A“.

## 3. Sum a Column of Cells Ignore #N/A Using SUM Array Formula

You can also use another formula to sum a column of cells ignore #N/A error based on the SUM function. Here is the formula:

``=SUM(IF(ISNUMBER(A1:A12),A1:A12,0))``

In a blank cell, type this formula, then Press Ctrl+Shift+Enter to enter the formula as an array formula.

This formula uses the SUM function with an array formula to sum only the cells in the specified range that are numbers. Any #N/A errors are treated as non-numeric and are converted to zero by the IF function.

## 4. Sum a Column of Cells Ignore #N/A with VBA code

If you want to quickly sum a column of cells or a selected range of cells while ignoring #N/A errors in Excel, you can also use an VBA code to achieve it. Just do the following steps:

Step1: pressing Alt+F11 shortcut to open the Visual Basic Editor in Excel

Step2:  click Insert menu to insert a new module named “Module1

Step3:  paste the code into the module.

```Sub SumCellsWithoutNAError_excelhow()
Dim rangeToSum As Range
Dim destination As Range
Dim sumResult As Variant

'Prompt the user to select the range of cells to sum
Set rangeToSum = Application.InputBox(prompt:="Select range to sum (ignoring #N/A errors):", Type:=8)

'Prompt the user to select the destination cell for the sum result
Set destination = Application.InputBox(prompt:="Select destination cell for sum result:", Type:=8)

'Calculate the sum of the selected range, ignoring any #N/A errors
sumResult = Application.WorksheetFunction.SumIf(rangeToSum, "<>#N/A")

'Insert the sum result into the selected destination cell
destination.Value = sumResult
End Sub```

Step4:  Press ALT + F8 to open the Macro dialog box. Then select the macro named “SumCellsWithoutNAError_excelhow” from the Macros list.

Step5:  select the range of cells to sum in your current worksheet.

Step6: select the destination cell for the sum result.

Step7: The macro will then calculate the sum of the selected range, ignoring any #N/A errors, and insert the result into the selected destination cell.

## 5. 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])…

## Vacation Budget Planner Template

The Vacation Budget Template is a very useful tool that can help you plan your trip to your dream destination. Vacations are an effective way to relieve the stress of our daily lives. You can take your family on a beach vacation or a picnic in the park , it will not only relieve the stress of life, but also increase the happiness of your family.

You will need to plan your budget based on your city or destination city. Because some places will have a lower cost of living, but some places will have a higher cost of living.

Before making a vacation budget, you need to set the maximum budget amount and after that choose the right destination. Based on the determined destinations, we need to find out the transportation methods and accommodation prices. When we finish these researches, we can start to make a vacation budget.

This article will introduce a free Excel vacation budget template that will help you plan the cost of your vacation and compare it to your total budget. You need to plan your vacation costs in advance so you know exactly how much we can spend in certain key categories.

## Vacation budget planner

This Vacation budget planner is officially provided by Microsoft Excel and you can create the template directly in the Excel application or download it from the official website.

You can use this vacation budget planner template to track your expenses for various events during the holidays.

This template only contains one worksheet: Budget Planner. This worksheet will help you plan your budget for your vacation, which contains budgets for each category.

Let’s see how to use this vacation budget template:

Step1: Open Microsoft Excel Spreadsheet, click on the File menu, and then click on the New submenu

Step2: In the New dialog box, find the template search box, type in the keyword ” Vacation budget planner” and press Enter, you will see the Vacation budget planner template.

Step3: Click the Create button to download the selected Vacation budget planner template to create a new Vacation budget planner spreadsheet.

Step4: You need to enter the estimated expense amount for each category in the category table and the actual expense will be calculated automatically by the SUMIF formula.

Step5: You need to enter the details of each category in the EXPENSE DETAILS table.

If you still need a customized budget template, you can send us a message.

If you are looking for Vacation budget planner template, check out Microsoft office site by clicking here.

## 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 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 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], …)…

## How to Sum Numbers by Formula if Cells Are Equal to A Certain Value in Excel

We may meet the case that sum numbers based on a certain value in daily work. To sum numbers based on a certain value, we can define this value as a criterion. Then sum up all numbers which meet the condition. The scenario can be simple or complex in real life. But no matter how complex it is, we can use proper excel function to resolve problem.

In this article, we will show you the resolution of ‘sum if cells equal to a certain value’ 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 for reaching the target. Actually, some other functions like SUMIF (or the combination of excel functions) can also resolve this issue, we will also mention SUMIF 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 SUMIFS function, and also a supplement of you excel skills

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 apple. That means we need to sum up data in column C which B column in the same row equals to ‘Apple’ exactly. We can apply a formula contains SUMIFS function to resolve this issue.

FORMULA – SUMIFS 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 =SUMIFS(Amount,Fruit,”Apple”).

Step 4: Press Enter after typing the formula.

We can see in column A there are three cells are equal to text ‘Apple’, the corresponding amounts are 100 (in cell C2), 150 (in cell C5) and 150 (in cell C8), so the total is 100+150+150=400. 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.

ALL ARGUMENTS

SUMIFS – SUM RANGE

In our instance, C2:C9 is the sum range obviously. 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, select ‘Amount’, press F9, values in this range are listed in an array.

SUMIFS – CRITERIA RANGE

B2:B9 is the criteria range. In this instance we have only one criteria range. This range contains three kinds of fruits.

In the formula bar, these fruits are displayed in proper order in an array:

SUMIFS – CRITERIA

We want to calculate total amount for ‘Apple’ only. We need to look up all cells equal to ‘Apple’ from criteria range. So, in criteria position we enter ‘Apple’. SUMIFS function supports texts, but they should be enclosed into double quotes “ “ when applying them.

HOW FORMULA WORKS

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.

`=SUMIFS({100;120;200;150;130;230;150;250},{"Apple";"Banana";"Orange";"Apple";"Banana";"Orange";"Apple";"Banana"},"Apple")`

See screenshot below:

As mentioned above, the criteria range, criteria are:

Criteria range: {“Apple”;”Banana”;”Orange”;”Apple”;”Banana”;”Orange”;”Apple”;”Banana”}

Criteria: “Apple”

In criteria range, “Apple” occurs three times, at the first position, the fourth position and the seventh position; So, these positions are marked with ‘True’ after comparing with text ‘Apple’, the others are not ‘Apple’, so they are marked with ‘False’.

So, after comparing we get below array:

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

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

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

Now, we have below two arrays:

Array 1: {100;120;200;150;130;230;150;250} – sum range

Array 2: {1;0;0;1;0;0;1;0} – if cell equals to ‘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 ‘Apple’; Array1 records amount for each kind of fruit in array2. Above all, numbers which are not zero in array3 are the amounts of ‘Apple’ after filtering. Now, sum up all values in array3.

100+0+0+150+0+0+150+0=400

Above analysis can help us know the process of this formula reaching the goal step by step clearly.

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 SUMIF function.

Enter =SUMIF(B2:B9,”Apple”,C2:C9) instead of applying SUMIFS formula.

We can get the same result. You can see that the order of arguments is a little different than SUMIFS function.

SUMMARY

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

2. SUMIFS function supports user defined range name.

### 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 Data if Begins with/End with/Contains in Excel

For a set of data, sometimes we may need to sum some of them based on criteria ‘if begins with/end with/contains’ ** characters. In our daily work, this case frequently occurs when calculating total for one or more items among a lot of different type of items.

To resolve this problem, we can apply excel SUMIF and SUMIFS functions. For these two functions, ‘criteria’ is an important argument, so through providing proper criteria range, criteria and sum range, we can filter out data matches the entered criteria and then sum data based on the filtered data.

In this article, we will show you the formula which can ‘sum data if begins/end with or contains ** characters’ based on SUMIF or SUMIFS functions. These two functions are frequently used in sum data with ‘Criteria’. We will introduce above two functions with simple examples, descriptions, screenshots and explanations in this article, and also show you the usage of them. As the two functions look similar, we will also introduce the difference between them. At last, we will let you know the function/formula workflow step by step clearly. After reading the following article, I’m sure you can have a simple understanding of SUMIF or SUMIFS functions.

EXAMPLE:

In above table, the first item is ‘Fresh Fruit’, so we can sum data based on criteria in A column which begins with ‘Fresh’; for the second item ‘Candy’, we can see ‘Orange Candy’, ’Grape Candy’ are end with ‘Candy’, so we can sum data based on criteria ends with ‘Candy’; for the third item ‘Apple’, ‘Fresh Apple’, ’Apple Juice’ and ‘Apple Pie’ contain ‘Apple’, so we can sum data based on criteria contains ‘Apple’. We will apply formula contains SUMIF or SUMIFS to sum data refer to above three scenarios.

FORMULA – SUMIF FUNCTION

Step 1: In E2, enter the formula =SUMIF(\$A\$2:\$A\$9,”Fresh*”,\$B\$2:\$B\$9). ‘Fresh’ is the criteria, we add an asterisk (*) after ‘Fresh’, it means one or more characters exist following word ‘Fresh’.

Step 2: Press Enter after typing the formula.

‘Fresh Apple:100’+’Fresh Orange:150’+’Fresh Grape:150’=400. We get 400 by applying the formula. So the formula works properly.

If we omit (*) in criteria ‘Fresh*’, 0 is returned as there is no criteria exactly equals to ‘Fresh’.

Step 3: In E3, enter the formula =SUMIF(\$A\$2:\$A\$9,”*Candy”,\$B\$2:\$B\$9). ‘Candy’ is the criteria, we add an asterisk (*) before ‘Candy’, it means one or more characters exist before word ‘Fresh’.

Step 4: Press Enter after typing the formula. ‘Orange Candy:200’+’Grape Candy:250’=450.

Step 5: In E4, enter the formula =SUMIF(\$A\$2:\$A\$9,”*Apple*”,\$B\$2:\$B\$9). ‘Apple’ is the criteria, we add asterisk (*) before and after ‘Apple’, then all contents contain ‘Apple’ are filtered, amounts for filtered ‘Apple’ are summed.

HOW FORMULA WORKS:

For SUMIF function, the syntax as below:

SUMIF(range, criteria, [sum range])

The first argument ‘range’ is the criteria range, in our instance, for three items, the criteria range is the same range ‘\$A\$2:\$A\$9’, we add \$ before range A2:A9 to lock this range, so when copy this formula to other cells, reference A2:A9 is will not be auto changed.

The second argument ‘criteria’ is the criteria we sum data based on. In above applied formulas, the criteria are “Fresh*”, “*Candy” and “*Apple*” respectively. It reflects that SUMIF function can support wildcards in its formula, and it requires to add double quotes (““) to enclose wildcards or texts. An asterisk (*) means one or more characters. A question mark (?) means one character. The position of asterisk or question mark means the character(s) position relative to the entered part.

The third argument ‘sum range’ lists values can be used for sum. Through the first two arguments, we can filter data in criteria range based on the criteria, after filtering, amount for the filtered data are summed.

This formula can be seen like this:

=SUMIF({“Fresh Apple”;”Apple Juice”;”Apple Pie”;”Fresh Orange”;”Orange Juice”;”Orange Candy”;”Fresh Grape”;”Grape Candy”},”Fresh*”,{100;120;200;150;130;200;150;250})

If texts in criteria range begins with ‘Fresh’, then the result is TRUE, otherwise the result is FALSE. So after comparing, the result can be seen as {TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE}. So the formula is equivalent to {1,0,0,1,0,0,1,0}*{100;120;200;150;130;200;150;250}={100,0,0,150,0,0,150,0}. SUM data in this array, we can get 400. In E2 the result is 400 as well.

Through above analysis process, we can also analysis the working process for the formulas in E3 and E4. They are exactly the same because we only apply SUMIF function here.

FORMULA – SUMIFS FUNCTION

Step 1: In E2 enter the formula =SUMIFS(\$B\$2:\$B\$9,\$A\$2:\$A\$9,”Fresh*”).

Step 2: In E3 enter the formula =SUMIFS(\$B\$2:\$B\$9,\$A\$2:\$A\$9,”*Candy”).

Step 3: In E4 enter the formula =SUMIFS(\$B\$2:\$B\$9,\$A\$2:\$A\$9,”*Apple*”).

After pressing Enter, we can see we get the same results by applying SUMIF function.

HOW FORMULA WORKS:

SUMIFS function looks similar to SUMIF, it only has an extra ‘S’ than SUMIF. SUMIF function can be seen as SUM+IF, it has only one group of ‘criteria range’ and ‘criteria’, but SUMIFS function can be seen SUM+ IFS, it can have multiple ‘criteria range’ and ‘criteria’ combinations.

For SUMIFS function, it has below arguments:

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

In our instance, \$B\$2:\$B\$9 is the sum range, it provides values to do sum calculation. If we want to set it as an absolute range, we can add \$ before column and row to lock the range. Then when copying formula to other cells, this range is fixed.

\$B\$2:\$B\$9 =({100;120;200;150;130;200;150;250}

\$A\$2:\$A\$9 is the criteria range, in this case we have only one criteria range.

{“Fresh Apple”;”Apple Juice”;”Apple Pie”;”Fresh Orange”;”Orange Juice”;”Orange Candy”;”Fresh Grape”;”Grape Candy”}

“Fresh*”, “*Candy” and “*Apple*” are criteria for the three formulas respectively. We compare each value in criteria range \$A\$2:\$A\$9 with the entered text, if the entered text can be found with correct position, corresponding amount value in sum range \$B\$2:\$B\$9 will be summed. By this way, we get total amount based on the criteria. Above all, the formula in E4 can be seen as below:

=SUMIFS({100;120;200;150;130;200;150;250},{“Fresh Apple”;”Apple Juice”;”Apple Pie”;”Fresh Orange”;”Orange Juice”;”Orange Candy”;”Fresh Grape”;”Grape Candy”},”*Apple*”)

For the following steps, you can refer to SUMIF function working process.

SUMMARY:

1. SUMIF function has only one group of criteria range and criteria, sum range is listed in the last position in the formula.
2. SUMIFS function has multiple groups of criteria ranges and criteria, sum range is listed in the first position of the formula.
3. They are frequently used in summing data based on criteria.

### 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 Group in Excel

Sometimes we may have the requirement that subtotal data by group in a table. To just subtotal data in a column, we can use SUM function directly; for data in a column, amount data in another column, we can use SUMIF function to subtotal data. But to subtotal data in group, we need to classify data by group firstly, then subtotal data which belongs to the same group. Above all, to subtotal data by group, we need to use formula based on IF function and SUMIF function. IF function is used for confirm ‘if value in a cell equals to its cell above’, if yes, then SUMIF function will be triggered and returns the corresponding total amount. This tutorial will help you to know the method with simple description, screenshots and explanation clearly. You can refer to below example for understanding well.

See example below:

You can see that fruits are classified to different groups, if you want to subtotal data by group, do you know the proper formula in this instance?

In C2, enter the formula =IF(A2=A1,” “,SUMIF(\$A\$2:\$A\$13,A2,\$B\$2:\$B\$13)).

In this instance, we use IF function to return the subtotal value. We know that for IF function, =IF(logical_test, [value if true], [value if false]). In this formula, we use A2=A1 to classify data, if the two values are the same, nothing will be returned due to they belong to the same group, if they are different, that means a new group is detected, the SUMIF function will be used here.

And for SUMIF function =SUMIF(range, criteria, [sum range]), in this instance, we need to subtotal data by group, \$A\$2:\$A\$3 is an absolute range for criteria, each data in column A is a criteria, \$B\$2:\$B\$3 is an absolute range for matching values, so if we enter SUM(\$A\$2:\$A\$3,A2, \$B\$2:\$B\$3) here, excel will search A2 value ‘Apple’ in \$A\$2:\$A\$3, and find all ‘Apple’ in this range, then subtotal all matching values in \$B\$2:\$B\$3, then return SUMIF value in C2 finally.

Click Enter to get returned value. We can see that 400 is correct.

Drag down the cell to copy formula. We can see that subtotal is recorded in the first row of each group. That’s because in this row, the data in column A is different with its cell above, a new group is found.

Notes:

1. In this instance, it is easy to locate the beginning data and ending data in a column, if there are multiple rows in the table and you are hard to find the ending data, you can use the full column as reference. Then formula will update to =IF(A2=A1,” “,SUMIF(A:A,A2,B:B)), it does work.

2. If you want to record subtotal in the last tow of each group, just enter the formula in the last row of the first group, and copy cell for the following cells.

### 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 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 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])….