Module 04: Connect and summarize data
Answer the same business question with a sum and a count
Use multiple criteria to total paid orders and verify how many records contributed to the answer.
Excel 2016, 2019, 2021, 2024, and Microsoft 365 for Windows desktop. SUMIFS and COUNTIFS do not require dynamic arrays. English formulas use commas; some locales require semicolons. Time is an estimate.
Useful first: Choose the right total, average, and count · Turn clear rules into IF, AND, and OR formulas · Copy formulas while keeping shared inputs fixed · Clean hidden spaces without losing the original text
- Apply several conditions to the same records.
- Pair SUMIFS with COUNTIFS to make a result easier to audit.
- Keep criteria and amount ranges aligned while changing the question.
Small data. A result you can check.
Synthetic data only. Paste at A1; create the criteria and report cells separately.
View the raw practice data
Region Category Amount Status North Parts 100 Paid South Parts 80 Paid North Service 60 Paid North Parts 40 Open North Parts 20 Paid South Service 30 Open
01Write the question as three visible criteria
Paste the sample into A1:D7. The columns are Region, Category, Amount, and Status. Enter Region, Category, and Status again in G1:I1, with North, Parts, and Paid in G2:I2. The question is now visible: how much belongs to records meeting all three conditions, and how many such records exist?
Manually identify the matching source rows before entering a formula. Rows 2 and 6 are North/Parts/Paid, with amounts 100 and 20. Row 5 has the same region and category but is Open, so its 40 must not enter this result. All amounts are ordinary integer numbers.
| Control | Expected before formulas |
|---|---|
| North, Parts, Paid | Amount 120 from 2 records |
| North, Parts, Open | Amount 40 from 1 record |
| All six source records | Amount 330 |
02Sum only rows that meet every condition
Enter MatchingAmount in K1 and the formula below in K2. SUMIFS starts with the amount range, then alternates each criteria range with its criterion. The dollar signs keep the source ranges and criteria cells fixed if you copy the formula elsewhere.
All four source ranges start at row 2 and end at row 7. Matching sizes are necessary, but matching meaning also matters: shifting the Status range down one row would compare a different record's status. Read the references as aligned row pairs rather than a collection of independent columns.
=SUMIFS($C$2:$C$7,$A$2:$A$7,$G$2,$B$2:$B$7,$H$2,$D$2:$D$7,$I$2)Enter in K2. With G2:I2 set to North, Parts, Paid, the result is 120.
03Count the contributing records separately
Enter MatchingRecords in L1. In L2 use =COUNTIFS($A$2:$A$7,$G$2,$B$2:$B$7,$H$2,$D$2:$D$7,$I$2). It returns 2 because it counts matching records, not their amounts. This companion result makes an unexpectedly large or small total easier to investigate.
A zero amount is not proof that no records matched: legitimate zero amounts or offsetting values can total zero. Check the count as well. These criteria functions also interpret wildcard characters in text criteria. Use controlled category labels here; a literal star in an identifier needs an explicit escape policy rather than casual reuse of this formula.
Change the criteria without editing either formula, and reconcile each answer.
- Keep North and Parts, but change I2 from Paid to Open. Inspect the source row responsible for the result.
- Change G2 to West while keeping Parts and Open. Then restore G2 to North and I2 to Paid.
- In N2 enter =SUM(C2:C7) as an independent full-file control and check that it remains unchanged as the criteria change.
Show the worked answer
North/Parts/Open gives K2 = 40 and L2 = 1.
West/Parts/Open gives 0 and 0. Restoring North/Parts/Paid gives 120 and 2.
N2 remains 330 because no source amounts changed.
Check your work- The three criteria combine with AND: each included record must satisfy all three.
- C2:C7 contains numbers, not numeric-looking text.
- Changing a criterion changes the report, not the six original records.
One question before you move on.
Ready for the next step?
Mark this lesson when you can explain the idea and reproduce the practice result.
Your checklist stays on this browser.Reference notes
These lessons use original examples. Check Microsoft’s documentation for details and platform-specific options.
Prepared September 13, 2026 · Column Harbor editorial team. Menu locations and available functions can vary by Excel version.