SUMIF and COUNTIF for Excel tests, with examples
These functions answer everyday questions, such as how many orders came from Boston or what those orders added up to. They come up often on intermediate Excel tests, and their rules are easy to mix up.
COUNTIF and SUMIF
COUNTIF counts the cells that meet a condition. For example, =COUNTIF(C2:C20, "Boston") counts the Boston entries in that column.
SUMIF adds up values that meet a condition. The range you check and the range you add can be different: =SUMIF(C2:C20, "Boston", D2:D20) checks column C for Boston and adds the matching values from column D.
Writing conditions correctly
A comparison needs quotes around the whole condition. This is the most common slip.
| You want | Write it like this |
|---|---|
| More than 3 | =COUNTIF(B2:B20, ">3") |
| 3 or fewer | =COUNTIF(B2:B20, "<=3") |
| Exactly the text Paid | =COUNTIF(C2:C20, "Paid") |
| Anything but Paid | =COUNTIF(C2:C20, "<>Paid") |
| Starts with North | =COUNTIF(C2:C20, "North*") |
| More than the value in E1 | =COUNTIF(B2:B20, ">"&E1) |
Two or more conditions: COUNTIFS and SUMIFS
Add an S to check several conditions at once. All of them have to be true for a row to count.
- COUNTIFS: =COUNTIFS(C2:C20, "Boston", B2:B20, ">3")
- SUMIFS: =SUMIFS(D2:D20, C2:C20, "Boston", B2:B20, ">3")
Watch the order. SUMIF puts the range to add last, while SUMIFS puts it first. Mixing these up is an easy way to lose marks, so practice both until the difference is automatic.
More guides
DeskReady is an independent practice tool, not affiliated with any software vendor or employer. Tests vary, so use this as a general guide.