## How to Use the SUMIFS Function in Excel

In this tutorial, you'll learn how to use the **Excel SUMIFS function** to sum cells that match multiple criteria.

When aggregating in Excel, you may want to calculate the sum of numerical values that satisfy multiple conditions.

The SUMIFS function allows you to find the sum of numbers that satisfy multiple conditions in cell ranges.

In Excel, the SUMIFS function is a function that allows you to specify multiple conditions and find the sum of all data that match more than one condition. For example, in a sales performance chart, you can find the total sales amount by specifying two conditions: "Product" and "Person in charge."

### SUMIFS() Syntax

The syntax of the SUMIFS() function is as follows:

=

SUMIFS(,sum_range,range1, [criteria1,range2], ...)criteria2

### Arguments

: The 1st range of cells that you want to be evaluated by*Range1**criteria1*: The 1st criteria to decide which cells in*Criteria1*will be added*range1*,*Range2**criteria2*, ... :: The range of cells to sum the values*Sum_range*

### Remarks

- You can specify the additional pairs of range and criteria up to 127 arguments.
- Text criteria or criteria, that includes mathematical or logical symbols, need to be enclosed in double quotation marks. If criteria is numeric, double quotation marks are not required.
- You can use the wildcard characters (*) and (?) in text criteria.
**SUMIFS()**is not case-sensitive. The criteria*JOHN*and*John*are the same.

### SUMIFS Function Examples

Demo data:

A | B | C | D | E | F | G | |

1 | Date | Code | Item Name | Quantity | Price ($) | Amount ($) | Employee |

2 | 7/20/2019 | Item0001 | HP Desktop PC 1 | 3 | 1250 | 3750 | Roland |

3 | 7/20/2019 | Item0002 | HP Desktop PC 2 | 2 | 1000 | 2000 | James |

4 | 7/20/2019 | Item0001 | HP Desktop PC 1 | 2 | 1150 | 2300 | James |

5 | 7/20/2019 | Item0003 | HP Desktop PC 3 | 2 | 450 | 900 | Roland |

6 | 7/21/2019 | Item0002 | HP Desktop PC 2 | 2 | 950 | 1900 | Roland |

7 | 7/22/2019 | Item0001 | HP Desktop PC 1 | 1 | 1180 | 1180 | Roland |

8 | 7/23/2019 | Item0003 | HP Desktop PC 3 | 2 | 440 | 880 | James |

#### Example 1: SUMIFS with a pair of range and criteria

The following example sums all the amount in column F where *the Date* is 7/20/2019:

=

SUMIFS(F2:F8,A2:A8,DATE(2019,7,20))

As you can see, there is only one pair of range and criteria in the above formula.

The formula returns 8950.

*Note that we use the **DATE function** as the criteria in this example.*

You can also use the text criteria, as the following example shows:

=

SUMIFS(F2:F8,A2:A8,"7/20/2019")

#### Example 2: SUMIFS() with 2 pairs of ranges and criteria

You can have 2 pairs of ranges and criteria in the SUMIFS function, as the following example shows:

=

SUMIFS(F2:F8,A2:A8,">=7/21/2019",G2:G8,"James")

**SUMIFS** sums all the *amounts* in the range F2: F8 that meet the following conditions:

- The
*date*is greater than*7/21/2019*in the range A2: A8 *The employee*must be*James*in the range G2: G8.

The above formula returns 880.

#### Example 3: SUMIFS between 2 dates

The following example shows how to sum values between two dates:

=

SUMIFS(F2:F8,A2:A8,">=7/21/2019",A2:A8,"<=7/23/2019")

As you can see, the 1st and 2nd criteria range (A2: A8) are the same, because we want to sum all values in the range F2: F8 with two conditions on the same column A, where** ***">=7/21/2019"* is the 1st criteria and *"<=7/23/2019"* is the 2nd criteria.

**SUMIFS**() sums all the value between *7/21/2019* and *7/23/2019* and returns 3960.

#### Example 4: SUMIFS with 3 conditions

Here is an example of SUMIFS() with three conditions:

=

SUMIFS(F2:F8,A2:A8,">=7/21/2019",A2:A8,"<=7/23/2019",G2:G8,"James")

**SUMIFS**() sums all values in the range F2: F8, where the *date* is between *7/21/2019* and *7/23/2019*, and the employee is *James*.

The above formula returns 880.

#### Example 5: SUMIFS is not case-sensitive

The SUMIFS function is not case-sensitive.

Here is an example:

=

SUMIFS(F2:F8,A2:A8,">=7/21/2019",A2:A8,"<=7/23/2019",G2:G8,"james")

The above formula returns the same result as the previous formula.

#### Example 6: Partial matching in criteria

You can perform partial matching in the SUMIFS function, as the following example shows:

=

SUMIFS(F2:F8,A2:A8,">=7/21/2019",A2:A8,"<=7/23/2019",G2:G8,"J*m??")

In the formula, the 3rd criteria, *J*m??* matches any text that begins with *J,* followed by *any sequence of characters* and then followed by *m*, and ends with *any 2 characters*.

The criteria *J*m??* matches for *James*.

Note that the wildcard character asterisk (*) matches any sequence of characters and question mark (?) matches any single character.

### Summary

In this tutorial, you've learned how to use the **Excel SUMIFS function** to sum the values in a range of cells that meet multiple criteria.

If you want to specify the condition and get the total value, use the SUMIFS and SUMIF functions.

Mastering various functions such as the SUMIF, SUMPRODUCT, COUNTIF, COUNTIFS, SUBTOTAL, and VLOOKUP functions will help you improve your Excel work efficiency.