SUMIF vs COUNTIF: Totals vs Counts

The short answer to SUMIF vs COUNTIF: SUMIF adds the numbers that meet a condition, and COUNTIF counts the cells that meet it. One gives you a total, the other a number of rows. They use exactly the same criteria, so once you know one, the other is easy. This guide shows both on the same expense data, so you can pick the right one quickly at work or in a job test.

SUMIF vs COUNTIF at a glance#

The key question is whether you need "how much" or "how many".

How the syntax differs#

The two functions share their first two arguments. SUMIF adds a third.

=COUNTIF(range, criteria)
=SUMIF(range, criteria, [sum_range])

• range is the cells to check, such as the Category column.
• criteria is what they must match: "Travel", 300, ">300" or a cell like G1.
• sum_range (SUMIF only) is the column of numbers to add for the matching rows.

COUNTIF never needs a third range, because it only counts cells in the range it checks. In SUMIF, sum_range is optional. If you leave it out, SUMIF adds the cells it checked, which only makes sense when they are numbers: =SUMIF(D2:D9,">300") adds every claim over 300. The SUMIF function guide and the COUNTIF function guide cover each argument.

The same data, solved both ways#

Here is a list of expense claims:

Question 1: travel claims#

How many travel claims are there, and how much do they add up to?

=COUNTIF(C2:C9,"Travel")
=SUMIF(C2:C9,"Travel",D2:D9)

COUNTIF returns 4 (rows 2, 5, 6 and 7). SUMIF returns 1,530 (420 + 310 + 260 + 540). Same range, same criteria; SUMIF just adds the Amount column for those rows.

Question 2: claims over 300#

Conditions on numbers work the same way in both. Put the operator and number together in quotes:

=COUNTIF(D2:D9,">300")
=SUMIF(D2:D9,">300")

COUNTIF returns 3 and SUMIF returns 1,270 (420 + 310 + 540). Here SUMIF needs no third argument, because the cells it checks are the cells it adds.

Question 3: one person's claims#

Point the criteria at a cell to reuse the formula. With Lena in G5:

=COUNTIF(A2:A9,G5)
=SUMIF(A2:A9,G5,D2:D9)

Lena made 2 claims worth 680 in total. Change G5 to Omar and both update: 2 claims worth 690.

Question 4: the average travel claim#

Divide the total by the count:

=SUMIF(C2:C9,"Travel",D2:D9)/COUNTIF(C2:C9,"Travel")

The result is 382.5 (1,530 ÷ 4). AVERAGEIF does the same in one step: =AVERAGEIF(C2:C9,"Travel",D2:D9). If nothing matches, both give #DIV/0!, because the count is 0.

Question 5: partial matches with wildcards#

Both functions accept wildcards in text criteria. A wildcard is a symbol that stands for other characters: * means any number of characters and ? means exactly one. To find every category that starts with "S":

=COUNTIF(C2:C9,"S*")
=SUMIF(C2:C9,"S*",D2:D9)

COUNTIF returns 2 and SUMIF returns 245 (150 + 95), both from the two Software claims. If the list later gains a "Subscriptions" category, both formulas pick it up without any change. The guide to wildcards in COUNTIF and SUMIF has more patterns.

When to use SUMIF#

Use SUMIF when the answer is an amount: money, hours, units or any other quantity. It fits questions like:

• Total spending by category or department. "How much did Sales claim?" =SUMIF(B2:B9,"Sales",D2:D9) returns 860.
• Totals above or below a limit. Every claim over 300, every order under 50.
• Units sold, not orders placed. If one order is for 12 boxes, SUMIF on the Units column gives 12; COUNTIF gives 1.
• Building a summary table, such as spending per category, with one SUMIF filled down a list of categories.

You can practise SUMIF in a live spreadsheet with a sales list.

When to use COUNTIF#

Use COUNTIF when the answer is a number of rows: how many items, people, orders or tickets. It fits questions like:

• How many claims, tickets or orders have a certain status or category.
• How many values pass a test, like scores of 70 or more.
• How many cells are filled in, with "<>" as the criteria. The guide to COUNTIF for non-blank cells covers this.
• Spotting duplicates, with =COUNTIF($A$2:$A$9,A2)>1.

COUNTIF works on any column, including text, so it's often the first function people learn for summaries. Practise COUNTIF to get quick with its criteria, and see how to use COUNTIF for more examples.

Common mistakes with SUMIF and COUNTIF#

These are the slips that most often give the wrong answer:

1. Using COUNTIF when you need a total. "How much travel?" with COUNTIF returns 4, not 1,530. Read the question: "how much" means SUMIF.
2. Giving COUNTIF a third argument. =COUNTIF(C2:C9,"Travel",D2:D9) shows a "too many arguments" message. Switch to SUMIF.
3. Forgetting SUMIF's sum range on a text column. =SUMIF(C2:C9,"Travel") checks and adds the Category column, which holds text, so the result is 0.
4. Leaving operators outside the quotes. Write ">300", not >300.
5. Putting a cell reference inside the quotes. Write ">"&G2, not ">G2". Inside quotes, G2 is just text.
6. Numbers stored as text. SUMIF skips text amounts, so totals come out low. Text numbers usually sit on the left of the cell.
7. Expecting case to matter. Neither function tells "travel" from "Travel". If capitals matter, you need a different approach, such as SUMPRODUCT with EXACT.

For more than one condition, such as Travel claims in Sales, neither function is enough. Use COUNTIFS and SUMIFS. The comparisons of COUNTIF vs COUNTIFS and SUMIF vs SUMIFS show how. If you were planning to use a lookup to pull a total, read VLOOKUP vs SUMIF first.

Our recommendation#

Choose by the question, not by habit. If the answer is a number of things, use COUNTIF. If the answer is an amount, use SUMIF. Learn them together, because the criteria rules are identical: quotes around text and operators, & to join a cell, * and ? as wildcards.

A good habit in a test is to say the question out loud with the unit. "How many claims?" has the unit claims, which you count. "How much in travel?" has the unit money, which you add. If the answer should have a currency sign or a unit like hours, it's a SUMIF. If it's a plain count of rows, it's a COUNTIF. When you report both, put the count next to the total, such as "4 claims, 1,530", so readers see the size of each group as well as its value.

In short: COUNTIF for how many, SUMIF for how much, and both together for an average.

Practise it#

Try the exercise on this page: it asks how much was spent, so COUNTIF won't do. Then practise SUMIF in a live spreadsheet and practise COUNTIF, so you can answer both kinds of question without stopping to think.