2.4 Counting and summing with conditions
Checked against Microsoft's Excel for Windows keyboard references, August 2026
What you'll be able to do
Answer "how much did this store sell in July" in one formula. The same shape handles any count, total or average with conditions on it, and it keeps answering as the data grows.
Before you start
Download the pivot sales workbook. It holds a sales table with stores, months and amounts, and the PivotTable topic uses it again.
The work
- Same either way: COUNTIF counts the rows that pass one test.
=COUNTIF(C:C, "Hyderabad")counts the Hyderabad rows. SUMIF adds up one column where another column passes the test:=SUMIF(C:C, "Hyderabad", E:E)gives the test column, the test, and the column to add. - Same either way: the plural forms take several conditions, and every one of them must hold. They read in pairs:
=SUMIFS(E:E, C:C, "Hyderabad", D:D, "July")gives the column to add first, then a range and its test, then another range and its test. That one formula answers this lesson's title question. Watch the order: SUMIFS starts with the column to add, and SUMIF ends with it. Write the plural forms even for one condition, and the confusion never arises. - Same either way: comparisons and dates go inside quotation marks.
=COUNTIFS(E:E, ">10000")counts the big sales.=SUMIFS(E:E, B:B, ">="&G1)joins a comparison to a date held in a cell, using the ampersand. The spelling is awkward once and then automatic. - Same either way: wildcards widen a text test. The star stands for any run of characters, and the question mark for exactly one.
=COUNTIF(A:A, "S4-*")counts everything that starts with S4-. - Same either way: AVERAGEIFS, MAXIFS and MINIFS complete the family with the same grammar. They give the average, the largest and the smallest of the rows that pass the conditions:
=AVERAGEIFS(E:E, C:C, "Hyderabad"). - Same either way: know when to stop writing these. One question and one number is a formula. The same question for every store in every month is a grid of twelve formulas with held references. That is a PivotTable's job: four clicks and no formulas at all. When you catch yourself filling a grid of SUMIFS, stop. The Analysis module's pivot topic takes it from there.
Try it
From the sales workbook, answer each of these with one formula. How many sales Hyderabad made, its July total, its average sale, and the number of sales above ten thousand. Then read each one aloud: "add the amounts where the store is Hyderabad and the month is July". Check that the formula says what you said.
Check yourself
Write the shape of "total sales for Hyderabad in July".
=SUMIFS(E:E, C:C, "Hyderabad", D:D, "July"). The column being added comes first, then each condition as a range and a test.
How does the order of parts differ between SUMIF and SUMIFS, and what is the safe habit?
SUMIF puts the column to add last, and SUMIFS puts it first. Always use the plural forms: they take more conditions and the order stays the same shape.
How do you count rows where the amount is more than the value in G1?
=COUNTIFS(E:E, ">"&G1). The comparison sign goes in quotation marks and is joined to the cell with an ampersand.
You are filling a grid of SUMIFS, with stores down and months across. What is the better tool, and why?
A PivotTable. It builds the whole grid from the raw table in a few clicks. It updates when refreshed, and there are no formulas to fill or break.
Go deeper
We haven't checked most of these for screen reader use yet.
Back to Counting and summing with conditions: work through the checklist