SUMIFS function

1 year ago

4 minutes

The SUMIFS function in Excel sums the values in a range based on multiple conditions (criteria). It is an extension of the SUMIF function, which allows you to specify more than one condition for filtering the data to be summed. This makes SUMIFS ideal for situations where you need to calculate totals based on multiple criteria.

Syntax

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

Parameters

sum_range (required): The range of cells to sum.
criteria_range1 (required): The first range to evaluate against the first condition.
criteria1 (required): The first condition that defines which cells in criteria_range1 will be summed.
[criteria_range2, criteria2, …] (optional): Additional ranges and conditions. You can specify up to 127 pairs of criteria ranges and criteria.

Key Points

Multiple Criteria: SUMIFS allows you to apply more than one condition. Each condition must refer to its own range (i.e., you can’t apply multiple criteria to the same range).
AND Logic: SUMIFS uses “AND” logic, meaning it sums values where all conditions are true.
Range Size: The sum_range and all criteria_range arguments must be the same size. If they are not, Excel will return an error.

Examples

Sum sales in range B1:B10 where corresponding values in A1:A10 are “Apple” and in C1:C10 are greater than 100:
```
=SUMIFS(B1:B10, A1:A10, "Apple", C1:C10, ">100")
```
Result: Sums all values in B1:B10 where the corresponding value in A1:A10 is “Apple” and the value in C1:C10 is greater than 100.
Sum the amounts in range B1:B10 where dates in A1:A10 are after January 1, 2024, and values in C1:C10 are “Yes”:
```
=SUMIFS(B1:B10, A1:A10, ">1/1/2024", C1:C10, "Yes")
```
Result: Sums all values in B1:B10 where the corresponding date in A1:A10 is after January 1, 2024, and the value in C1:C10 is “Yes”.
Sum values in range D1:D10 where corresponding categories in C1:C10 are “Electronics” and regions in B1:B10 are “West”:
```
=SUMIFS(D1:D10, C1:C10, "Electronics", B1:B10, "West")
```
Result: Sums values in D1:D10 where the corresponding category in C1:C10 is “Electronics” and region in B1:B10 is “West”.
Sum the total sales in range B1:B10 where the sales value is greater than 200 and the sales agent (in A1:A10) is “John”:
```
=SUMIFS(B1:B10, A1:A10, "John", B1:B10, ">200")
```
Result: Sums all sales in B1:B10 where the sales agent is “John” and the sales value is greater than 200.
Sum values where the item in column A is “Apple” and the date in column B is between January 1, 2024, and January 31, 2024:
```
=SUMIFS(C1:C10, A1:A10, "Apple", B1:B10, ">=1/1/2024", B1:B10, "<=1/31/2024")
```
Result: Sums values in C1:C10 where the corresponding value in A1:A10 is “Apple” and the date in B1:B10 falls between January 1, 2024, and January 31, 2024.

Notes

Multiple Criteria: Each criteria_range and criteria combination represents one condition. You can use as many conditions as needed, but each additional condition must specify both a range and a condition.
Criteria with Logical Operators: You can use logical operators like >, <, >=, <=, <>, and = in the criteria argument. These must be enclosed in quotation marks (e.g., ">100").
Date Criteria: Dates must be provided in a recognizable date format (e.g., "1/1/2024") or referenced from a cell containing a date.
Wildcards: You can use wildcards in text criteria. For example:
- * (asterisk) for any sequence of characters.
- ? (question mark) for a single character.

Related Functions

SUMIF: Similar to SUMIFS, but for a single condition. SUMIFS is the more flexible option when you need to evaluate multiple conditions.
COUNTIFS: Counts the number of cells that meet multiple conditions, instead of summing them.
AVERAGEIFS: Averages the values in a range based on multiple conditions.
SUM: Adds up all the values in a range, without any conditions.

The SUMIFS function is very powerful for conditional summation in large datasets, where you need to apply multiple filters to calculate totals based on different criteria.

Post Views: 20