SUMIFS and COUNTIFS: worked spreadsheet examples
Build conditional sales totals and task counts with SUMIFS and COUNTIFS, using small worked examples, aligned ranges, and checks for missing matches.
Use SUMIFS when matching records contribute an amount, and COUNTIFS when each matching record contributes one count. Keep the sum range and every criteria range aligned to the same source rows. The following examples use fictional records, so you can check the answers by hand before changing the formulas.
How do you total sales for one region with SUMIFS?
Suppose column B holds region, D holds quantity, E holds unit price, and F holds line sales. Put the formula =D11*E11 in F11 and copy it down.
| Row | Region | Quantity | Unit price | Line sales |
|---|---|---|---|---|
| 11 | East | 3 | 100 | 300 |
| 12 | West | 2 | 100 | 200 |
| 13 | East | 5 | 20 | 100 |
Enter East in B8. The formula =SUMIFS(F11:F70,B11:B70,$B$8) returns 400, adding 300 and 100. Changing B8 to West returns 200. This example is included in the SUMIFS sales workbook.
The sum range comes first in SUMIFS. Follow it with pairs of criteria range and criterion. That argument order differs from SUMIF. Microsoft documents both the syntax and the requirement that the ranges have matching dimensions in its SUMIFS reference.
How do you apply two conditions with SUMIFS?
If product names are in C11:C70, use =SUMIFS(F11:F70,B11:B70,$B$8,C11:C70,"Kit") to total kits sold in the selected region. The region condition and product condition must both hold on the same record.
For the table above, an East kit total is 300. The East part contributes nothing to this calculation even though it matches the region. Keep that excluded record in the source table so the reason for the difference remains visible.
How do you count open tasks for one owner?
For a task table with owner in B and status in C, use =COUNTIFS(B11:B70,$B$8,C11:C70,"Open"). With B8 set to Alex, it counts only Alex’s open tasks.
| Task | Owner | Status | Included? |
|---|---|---|---|
| Draft scope | Alex | Open | Yes |
| Review quote | Sam | Open | No |
| Send report | Alex | Complete | No |
| Confirm test | Alex | Open | Yes |
The result is 2. It is a task count, not a total of estimated hours. The COUNTIFS task workbook includes a row flag alongside the summary, so each included record is visible. Microsoft’s counting guide explains the distinction between COUNT, COUNTIF, and COUNTIFS.
Why can a conditional total unexpectedly return zero?
A zero can mean no records matched, matched amounts were all zero, or the positive and negative amounts canceled. Check a corresponding record count before treating zero as evidence of no activity. Also inspect trailing spaces, numeric amounts stored as text, and source ranges that omit the newest records.
When extending a table, extend every range together. A sum range ending at row 70 and a criteria range ending at row 71 do not describe the same population. Keep category criteria in visible input cells and anchor those cells when copying the formula.