SUMIFS

The SUMIFS function is one of the most useful and powerful Excel functions for data analysis. It allows you to sum values from a specific range that meet multiple criteria simultaneously. If you work with large volumes of data and need to perform complex calculations with conditions, this function will become your indispensable assistant.

What is the SUMIFS function?

SUMIFS is a function that sums cells in a range that meet several specified conditions. Unlike SUMIF, which works with only one criterion, SUMIFS can check up to 127 different conditions in different ranges.

Function syntax

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

• sum_range — range of cells to sum
• criteria_range1 — first range to which the condition applies
• criteria1 — first condition in the form of a number, expression, or text
• criteria_range2, criteria2, … — additional pairs of ranges and conditions

Function features

Here are important rules and features of formulas with the SUMIFS function.

Range sizes

All ranges in the SUMIFS function must be the same size. If the sum range has 10 rows, then all criteria ranges must also contain 10 rows.

Text criteria

Text criteria must be enclosed in double quotes. If you reference a cell with a criterion, quotes are not needed.

=SUMIFS(B2:B100, A2:A100, D1)

Case sensitivity

The SUMIFS function is not case-sensitive. Conditions “PRODUCT”, “product”, and “Product” will be treated the same.

SUMIFS usage examples

Download the file with all examples using this link.

Example 1: Sales sum by region and product

Suppose we have a sales table and we want to know the sum of sales for the product “Table” in the “North” region.

=SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Table")

Example 2: Sales sum above specific value

Let’s say we need to calculate the sum of all sales that are greater than 50,000 rubles and were made by manager “Ivanov”.

=SUMIFS(D2:D100, D2:D100, ">50000", E2:E100, "Ivanov")

Using comparison operators

You can use various comparison operators in SUMIFS criteria:

Example 3: Sales sum in specific range

Let’s find the sum of sales from 10,000 to 50,000 rubles for the first quarter.

=SUMIFS(B2:B100, B2:B100, ">=10000", B2:B100, "<=50000", C2:C100, "January")

Example 4: Sales sum excluding specific product

Let’s calculate the sum of all sales except the product “Chair”.

=SUMIFS(D2:D100, B2:B100, "<>Chair")

Using wildcards

You can use wildcards in SUMIFS criteria:

• * — any number of any characters
• ? — one any character

Example 5: Sales sum of products starting with specific letter

Let’s find the sum of sales of all products whose name starts with “K”.

=SUMIFS(C2:C100, A2:A100, "K*")

Example 6: Sales sum of products with specific pattern in name

Let’s find the sum of sales of products that have “table” anywhere in their name.

=SUMIFS(D2:D100, B2:B100, "*table*")

Example 7: Sales analysis by dates and categories

Suppose we need to calculate the sum of sales for the “Electronics” category for the first half of 2024.

=SUMIFS(E2:E1000, C2:C1000, "Electronics", D2:D1000, ">=01/01/2024", D2:D1000, "<=06/30/2024")

Example 8: Summing with multiple exclusions

Let’s calculate the sum of sales for all regions except “Center” and “South”.

=SUMIFS(F2:F500, A2:A500, "<>Center", A2:A500, "<>South")

Conclusion

The SUMIFS function is a powerful tool for multi-factor data analysis in Excel. By mastering it, you can efficiently sum data based on multiple conditions and get accurate results for making informed business decisions.

This post is also available in RU.