Excel SUMIFS and COUNTIFS: Combine More Than One Condition
On this page
SUMIFS and COUNTIFS answer questions where a record must match several conditions. Their conditions work together as AND. The most important checks are argument order and keeping every criteria range aligned with the same source rows.
Practice setup: Start in a new workbook with fictional data. Formulas use English function names and commas; your regional settings may require semicolons. Interface labels vary between Excel versions. Keep the source data and check your results before reusing the method.
Enter six sales records
Use Region, Category and Amount in A1:C7. Enter North, Stationery, 360; South, Stationery, 250; North, Home, 600; North, Stationery, 320; South, Home, 450; and North, Tech, 800.
The full total is 2780 across six records. We first want the Stationery amount for North, not every North sale and not every Stationery sale.
Sum matching rows
=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Stationery") returns 680, from 360 and 320. SUMIFS puts the sum range first, then pairs of criteria range and criterion. This differs from SUMIF's argument order.
Read it as a sentence: add Amount where Region is North and Category is Stationery. If you cannot explain the formula that way, inspect the ranges before trusting the number.
Count the same records
=COUNTIFS(A2:A7,"North",B2:B7,"Stationery") returns 2. COUNTIFS has no sum range. This is a count of sale records, not distinct customers or products.
The ranges must have matching dimensions and row alignment. A2:A7 and B3:B8 do not describe the same records. A plausible answer does not prove the conditions were attached to the right rows.
Use criteria cells
Put North in E2 and Stationery in F2. Use =SUMIFS($C$2:$C$7,$A$2:$A$7,E2,$B$2:$B$7,F2) in G2. Change F2 to Home and the result becomes 600. Change E2 to South and it becomes 450.
Fixed source ranges stay in place while criteria cells can move when copied. Controlled drop-down choices can reduce spelling differences in criteria. New source records still need to be included in the input ranges or an expanding Table reference.
Test a numeric boundary
=COUNTIFS(A2:A7,"North",C2:C7,">=500") returns 2, for amounts 600 and 800. If the threshold is in H2, use ">="&H2. Quoting ">=H2" would include the letters H2 instead of reading the cell value.
Do not add North and South as two conditions on the same region range expecting OR. A single record cannot equal both distinct labels. An OR summary needs a different design.
Reconcile the report
North totals 2080 and South totals 700. Together they make 2780. This works because the two groups cover every sample row without overlap. An unexpected region spelling would leave a gap in that reconciliation.
Try it yourself
Count and sum South records with amounts at least 300. Then change the rule to greater than 450. Does an amount equal to 450 remain?
Check your answer
=COUNTIFS(A2:A7,"South",C2:C7,">=300") returns 1. =SUMIFS(C2:C7,A2:A7,"South",C2:C7,">=300") returns 450.
The rule >450 excludes the exact 450 boundary, leaving count 0 and sum 0. Include an equal-to-threshold case when testing a report so you can distinguish > from >=.
Keep exploring: Excel learning path.