How to Use the SUMIFS Function in Excel to Add Multiple Conditions

The standard SUM function in Excel is perfect for quickly adding up an entire column of numbers. But what if you only want to sum numbers that meet a specific criteria? For example, you have a massive sales spreadsheet, and you only want to add up the revenue generated by the “North” region. For that, you use SUMIF.

However, real-world data is rarely that simple. What if you need to know the total revenue generated by the “North” region, specifically for “Product A”, only during “Q3”? When you need to test multiple criteria simultaneously before adding numbers together, you must use the powerful SUMIFS function.

Understanding the SUMIFS Syntax

The SUMIFS function can handle up to 127 pairs of conditions, making it one of the most robust data analysis tools in Excel. The syntax looks slightly different than the older SUMIF function, primarily because the range you actually want to sum is placed at the very beginning of the formula.

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

• sum_range: The actual column of numbers you want Excel to add together.
• criteria_range1: The column you want Excel to look at to test your first condition.
• criteria1: The specific condition that must be met in criteria_range1.
• criteria_range2, criteria2: (Optional) The second column to test, and its specific condition.

Example: Summing with Two Text Conditions

Imagine you have a sales tracking sheet:

• Column C contains the Revenue amounts (the numbers to sum).
• Column A contains the Sales Region (e.g., North, South).
• Column B contains the Product Category (e.g., Hardware, Software).

You want to find the total revenue for “Hardware” sales in the “South” region.

Click an empty cell and type:
=SUMIFS(C2:C100, A2:A100, "South", B2:B100, "Hardware")

How Excel processes this: It first looks at Column A. If a row says “South”, it checks Column B. If that same row also says “Hardware”, it takes the corresponding number from Column C and adds it to the running total. If either condition fails, the number is ignored.

Example: Using Logical Operators (Greater Than / Less Than)

The SUMIFS function is not limited to matching exact words. You can use logical operators (>, <, >=, <=, <>) to evaluate dates or numbers.

Assume you want to sum the revenue in Column C, but only for sales that occurred after January 1st, 2023 (Dates in Column D), and only if the individual sale was greater than $500.

=SUMIFS(C2:C100, D2:D100, ">1/1/2023", C2:C100, ">500")

Notice two critical rules here:

1. When using logical operators inside a SUMIFS formula, the operator and the value must be enclosed in double quotes (e.g., ">500").
2. You can use the sum_range itself as one of the criteria_ranges. In this example, Column C is being tested for a condition and acting as the column being summed.

Using Cell References for Dynamic Formulas

Hardcoding words like “South” into your formulas is inefficient. If you change your mind and want to check the “North” region, you have to rewrite the formula. Instead, type the word “South” into an empty cell (e.g., F1), and reference that cell in your formula.

=SUMIFS(C2:C100, A2:A100, F1, B2:B100, "Hardware")

Because you are referencing a cell (F1) rather than typing a hardcoded word, you do not use quotation marks around the cell reference. Now, if you type “North” into cell F1, the formula will automatically recalculate the new total instantly.