
Tutorials
SUMIF, COUNTIF and SUMIFS Explained With Simple Examples
Learn how to add and count only the rows that match. Clear examples of COUNTIF, SUMIF, SUMIFS and COUNTIFS, including criteria and wildcards.
By ExcelPanda Team · Published · 6 min read
The examples below use a small sales table: Region in column A, Product in column B and Sales in column C, with data in rows 2 to 11.
COUNTIF: count the matches
How many rows are in the North region?
=COUNTIF(A2:A11, "North")Excel checks each cell in A2:A11 and counts those equal to North.
SUMIF: add up the matches
What are the total sales for the North region?
=SUMIF(A2:A11, "North", C2:C11)The first range is where Excel looks for the match. The last range is what it adds up. If the two are the same, you can leave the last one out.
Writing criteria
Common criteria
| You want | Criteria | Example |
|---|---|---|
| Exact text | "North" | =COUNTIF(A2:A11,"North") |
| Greater than a number | ">100" | =COUNTIF(C2:C11,">100") |
| Anything except | "<>North" | =COUNTIF(A2:A11,"<>North") |
| Starts with | "Pro*" | =COUNTIF(B2:B11,"Pro*") |
| Value in another cell | ">"&E1 | =COUNTIF(C2:C11,">"&E1) |
Operators go inside the quotation marks. To compare against a cell, join the operator and the cell with &.
SUMIFS and COUNTIFS: more than one condition
Total sales for the North region and the Pro product:
=SUMIFS(C2:C11, A2:A11, "North", B2:B11, "Pro")How many North rows had sales above 100?
=COUNTIFS(A2:A11, "North", C2:C11, ">100")In SUMIFS the range to add comes first, then pairs of range and criteria. Every range must be the same size. Once this feels easy, a PivotTable is the next step for summarising lots of categories at once.
Common mistakes
- Ranges of different sizes. SUMIFS returns #VALUE! if they do not match.
- Missing quotation marks. Text and operators need them: "North" and ">100".
- Numbers stored as text. A sales figure that is really text will not be added.
- Stray spaces. "North " with a trailing space is not the same as "North".
Frequently asked questions
What is the difference between SUMIF and SUMIFS?
SUMIF takes one condition. SUMIFS takes several and adds only the rows that meet all of them. Note that the argument order differs: SUMIFS puts the sum range first.
Are COUNTIF and SUMIF case sensitive?
No. "north" and "North" count as the same text.
How do I count cells that are not empty?
Use COUNTIF with the criterion "<>" (not equal to nothing), or use COUNTA on the range.
Can I use wildcards?
Yes. An asterisk matches any run of characters and a question mark matches one character, so "Pro*" matches Product and Project.
How do I match OR instead of AND?
Add two functions together, for example =COUNTIF(A:A,"North")+COUNTIF(A:A,"South"). SUMIFS and COUNTIFS always apply AND.
Sources and disclaimers
ExcelPanda is not affiliated with or endorsed by Microsoft. Microsoft and Excel are trademarks of the Microsoft group of companies.
.png)