Excel SUMIF and COUNTIF: Summarise Records That Match a Rule
On this page
You do not always need a total for every row. You may want the total for one category or a count of orders above a threshold. Excel SUMIF and COUNTIF answer these questions without manually selecting scattered cells.
Before you start: Use a new practice workbook. The names and figures below are fictional. Enter the headers in row 1 and the first record in row 2 unless a step says otherwise. Formulas use English function names and commas; your regional settings may require semicolons instead. Save a copy before experimenting.
Build a small sales list
Enter the data in A1:C7. Each row is one sale, so the same category can appear more than once. The category labels must use consistent spelling for a category summary to be meaningful.
| Item | Category | Amount |
|---|---|---|
| Notebook | Stationery | 360 |
| Pen | Stationery | 250 |
| Mug | Home | 600 |
| Folder | Stationery | 320 |
| Towel | Home | 450 |
| Cable | Tech | 800 |
The total across all six sales is 2780. Keep that figure as a check while building smaller summaries. It is easier to catch an omitted category when you know what the pieces should add up to.
Count rows that match a category
=COUNTIF(B2:B7,"Stationery") returns 3. The first argument is the range to test. The second is the condition, called the criteria argument. Text criteria go inside double quotes.
COUNTIF counts matching cells, not unique products or customers. If the same product is listed twice in two separate sale rows, both matching rows count. Before interpreting a count, decide what one data row represents.
Sum the amounts for the same category
=SUMIF(B2:B7,"Stationery",C2:C7) returns 930. SUMIF checks B2:B7, finds the rows labelled Stationery and adds the corresponding amounts from C2:C7: 360 + 250 + 320.
The criteria range and sum range should line up row by row and have the same dimensions. If one starts at row 2 and the other starts at row 3, the formula can associate a category with the wrong amount. A formula returning a number does not prove that the range alignment is correct.
Use a criteria cell instead of rewriting formulas
Type Stationery in E2. In F2 use =COUNTIF($B$2:$B$7,E2) and in G2 use =SUMIF($B$2:$B$7,E2,$C$2:$C$7). Put Home in E3 and Tech in E4, then copy F2:G2 down to row 4.
The fixed ranges stay on the dataset while E2 moves to E3 and E4. Home should have 2 rows totalling 1050. Tech should have 1 row totalling 800. The counts add to 6, and the totals add to 2780.
This small summary is easier to audit than manually selecting non-adjacent amounts. When new data is added, remember to extend the ranges or use an Excel Table so the input references can expand with the data.
Match a numeric condition
=COUNTIF(C2:C7,">=500") returns 2 because 600 and 800 qualify. The operator is part of the quoted criteria string. To keep a threshold in E6, enter 500 there and use =COUNTIF(C2:C7,">="&E6).
The ampersand joins the operator text to the current threshold value. Writing ">=E6" inside quotes would put the letters E6 into the criteria instead of reading that cell's value. Small punctuation differences can change the question.
For one condition, SUMIF and COUNTIF are enough. For several conditions that must all be met, look at SUMIFS or COUNTIFS. Note that SUMIFS puts its sum range first, unlike SUMIF. Do not transfer argument order from one function without checking.
Clean labels before trusting the summary
An extra trailing space can make a label different from what you intended. COUNTIF is not case-sensitive, but it does not fix inconsistent category names or all invisible characters. Keep a controlled list of categories and inspect unmatched records when totals do not reconcile.
Try it yourself
Count sales below 400 and calculate their combined amount. Then change the Stationery amount 250 to 500. Predict how that change affects both the Stationery total and the count of sales worth at least 500.
Check your answer
Initially, =COUNTIF(C2:C7,"<400") returns 3 and =SUMIF(C2:C7,"<400",C2:C7) returns 930. The qualifying values are 360, 250 and 320.
Changing 250 to 500 raises the Stationery total from 930 to 1180. The count of amounts at least 500 rises from 2 to 3. If one summary changes but another does not, check that both formulas include the changed row.
Keep learning: Return to the Excel learning path to choose your next lesson.