SUMIFS Formula Generator
Add as many criteria as you need and get a SUMIFS formula with the arguments in the right order.
When to use SUMIFS
- Totalling one region, one month, or one account code
- Adding up rows that meet several conditions at once
- Building a summary table from a long transaction list
How the formula works
Taking the example on this page apart, piece by piece:
=SUMIFS($C$2:$C$3,$B$2:$B$3,"East")
$C$2:$C$3The numbers to add up. In SUMIFS this comes first; in SUMIF it comes last.
$B$2:$B$3The condition: the range to check on each row.
"East"Only rows where $B$2:$B$3 is East. Text goes in quotes.
Where it goes wrong
Putting the sum range last
SUMIF takes the sum range last; SUMIFS takes it first. Swapping them usually returns a number rather than an error, so it looks like it worked.
Leaving the operator outside the quotes
A criterion carrying a comparison has to be quoted whole. To compare against a cell instead, join the operator to it with an ampersand.
Ranges of different heights
Every criteria range has to cover the same rows as the sum range, or Excel returns #VALUE!.
Practise it
A generator gives you the formula. These give you the reason it works — and tell you which assumption broke when it does not.
- Total sales for a region and month with SUMIFS · intermediate