Several conditions with SUMIFS and COUNTIFS
8 minutes · 4 steps · free, no sign-up
SUMIF and COUNTIF check one thing. Real questions usually check several: sales in the North for one product, or South orders above a certain size. SUMIFS and COUNTIFS handle those.
- Add up values that meet two conditions
- Count rows that meet two conditions
- Use the same range as both sum and condition
- See results update when data changes
Click a cell and start typing. Enter edits, Tab moves right, arrows move, Delete clears.
Quick referenceThe multi-condition functions
=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2, ...])
=COUNTIFS(criteria_range1, criterion1, [criteria_range2, criterion2, ...])
Note: SUMIFS puts the range to add up FIRST.The key ideasSummary: Several conditions with SUMIFS and COUNTIFS
SUMIFS and COUNTIFS take pairs of arguments: a range to check, then the condition to check it against. A row only counts when every pair matches. All the ranges must be the same size.
The one thing that trips people up is argument order. In SUMIF the range to add up comes last, but in SUMIFS it comes first. Conditions can be words like "North" or comparisons like ">=90".
Watch out forCommon mistakes
- Mixing up the argument order.
SUMIFputs the range to add last, butSUMIFSputs it first. This is the single most common SUMIFS error. - Ranges of different sizes. Every range in the formula must be the same height, otherwise you get a
#VALUE!error. - Expecting "or" instead of "and". A row must satisfy every condition to count. To add up North or South, add two SUMIFS together.
Next stepTry it in the real Google Sheets
Conditions do not have to be typed into the formula. Put a threshold in a cell, say F1, and join it onto the operator: =COUNTIFS(C2:C7,">="&F1). Now you can change F1 and every result updates, with no editing of the formula. You can try this exact pattern in the practice sheet above.