- Sep 23
Using SUM and SUMIFS for Multiple Criteria in Excel
- Neale Blackwood
- Dynamic Arrays, Formulas
- 0 comments
SUMIFS is great for summing values based on multiple conditions. But when one of those conditions needs to accept multiple options, such as two or more states, combining SUM with SUMIFS can provide a flexible solution.
Sign up to hear about free Excel training.
I won't share your email with anyone.
1 min read
In the image below we have a simple data table on the left and two yellow input cells on the right and a SUMIFS function in cell G2.
Notice that the SUMIFS result spills across to the right, returning one result for each state in the yellow criteria cells.
Wrapping a SUM function around the SUMIFS function will convert the spill range into a single cell result for both states.
Formula in cell G2.
=SUM(SUMIFS(C2:C9,B2:B9,E2:F2))This is a flexible and scalable technique.
If I insert a column between WA and NSW and enter another state the formula still works.
This simple technique provides many solutions when summing with multiple criteria and different structures.