Excel Basics›Core· 35 min read

Counting with COUNT, COUNTIF & SUMIF

COUNT tallies cells, while COUNTIF and SUMIF count or add only the rows that match a condition.

What you will learn

Count numbers and non-empty cells
Count rows that meet a condition with COUNTIF
Add only matching rows with SUMIF

Counting cells

Sometimes you do not want a total — you want to know how many things there are. Two functions handle this:

COUNT counts how many cells in a range hold a number.
COUNTA counts how many cells are not empty (numbers or text).

If A2:A6 contains Apples, Bananas, 120, blank, 90, then =COUNT(A2:A6) gives 2 (only the two numbers), while =COUNTA(A2:A6) gives 4 (everything except the blank).

COUNTIF: count only what matches

The really useful one is COUNTIF. It counts only the cells that meet a condition you give it. It takes two parts: the range to look in, and the condition to match.

Imagine a column of order statuses in C2:C8. To count how many say Shipped:

Count how many orders have the status Shipped

=COUNTIF(C2:C8, "Shipped")

Note: Output: If the column holds Shipped, Pending, Shipped, Shipped, Pending, Shipped, Pending, the cell shows 4. COUNTIF looked through C2:C8 and counted only the cells that exactly matched Shipped.

The condition can be a number comparison too. To count how many sales were above 100, put the condition in quotes: =COUNTIF(B2:B8, ">100").

SUMIF: add only what matches

SUMIF is COUNTIF’s partner. Instead of counting matching rows, it adds up numbers from matching rows. It takes three parts: the range to test, the condition, and the range to add.

Suppose column A is the region and column B is the sales. To total only the sales for the North region:

Add up the sales (column B) only for North rows (column A)

=SUMIF(A2:A8, "North", B2:B8)

Note: Output: If North rows have sales of 200, 150 and 50, the cell shows 400. SUMIF checked column A for North, and for every match it added the matching number from column B. Other regions were ignored.

Function	Question it answers	Parts it needs
`COUNT`	How many numbers are here?	range
`COUNTA`	How many non-empty cells?	range
`COUNTIF`	How many rows match a rule?	range, condition
`SUMIF`	What is the total of matching rows?	test range, condition, sum range

Tip: Put text and comparison conditions in double quotes: "Shipped", ">100", "<>0". A plain number like 50 does not need quotes, but it is safe to quote it anyway.

Q. You have regions in A2:A8 and sales in B2:B8. Which formula totals only the South sales?

Answer: SUMIF tests column A for South and adds the matching numbers from column B. COUNTIF would only count the rows, not total the sales.

✍️ Practice

Make a list of order statuses (Shipped/Pending) and use COUNTIF to count the Shipped ones.
Add a region column and a sales column, then use SUMIF to total the sales for one region.

🏠 Homework

Build a small sales table with three regions. Use COUNTIF to count the orders per region and SUMIF to total the sales per region.