Conditional Sums and Counts

Introduction

SUM adds everything. Often you want to add only some rows: revenue for one region, sales of one product, the number of rows above a limit. Conditional functions do this in one formula. The figures below use the same sales table as before, and have been checked by calculation.

Data recap (rows 2 to 9):

	A Month	B Region	C Product	D Units	E Price	F Revenue
2	Jan	N	Pen	10	2.00	20
3	Jan	S	Pen	5	2.00	10
4	Feb	N	Book	3	12.50	37.5
5	Feb	S	Book	4	12.50	50
6	Mar	N	Pen	8	2.00	16
7	Mar	S	Book	2	12.50	25
8	Mar	N	Bag	1	30.00	30
9	Apr	S	Bag	2	30.00	60

SUMIF: add where a condition is met

=SUMIF(B2:B9, "N", F2:F9)

The inputs are: the range to test, the condition, and the range to add. This adds the revenue where the region is N: 20 + 37.5 + 16 + 30 = 103.5.

For region S: 10 + 50 + 25 + 60 = 145. Together they make 248.5, the same as the total, which is a good check.

You can use a cell for the condition, so the formula follows the cell:

=SUMIF(B2:B9, H1, F2:F9)

Conditions can include comparisons in quotes, such as ">=30".

COUNTIF and AVERAGEIF

=COUNTIF(C2:C9, "Book")           → 3
=SUMIF(C2:C9, "Pen", D2:D9)       → 23
=AVERAGEIF(F2:F9, ">=30")         → 44.375

Three rows are books.
The pens sold are 10 + 5 + 8 = 23 units.
The revenues of 30 or more are 37.5, 50, 30 and 60, which add to 177.5, so the average is 177.5 ÷ 4 = 44.375.

Quick quiz

Which formula counts the rows where the product is Pen?

SUMIFS and COUNTIFS: several conditions

When you need more than one rule, use the versions ending in S. In SUMIFS, the range to add comes first:

=SUMIFS(F2:F9, B2:B9, "N", C2:C9, "Pen")

This adds revenue where region is N and product is Pen: 20 + 16 = 36.

=COUNTIFS(D2:D9, ">=4", B2:B9, "S")

This counts rows with 4 or more units in region S. Rows 3 (5 units) and 5 (4 units) match, so the answer is 2.

All conditions must be true at once (AND logic). For OR logic, add two formulas together.

Wildcards

In many conditions, * stands for any text and ? for one character: "B*" matches Book and Bag. Check how your program treats wildcards in its settings.

Check your results

Always sanity-check:

Do the parts add up to the total? (103.5 + 145 = 248.5)
Does a filter on the data show the same numbers?
Try a small example you can calculate in your head.

If numbers do not match, look for extra spaces, numbers stored as text, or different spellings.

Good practice

Put conditions in cells instead of typing them in formulas.
Use absolute references for ranges you copy.
Use whole, consistent ranges. Mismatched sizes cause errors.
Add a check cell that compares the sum of parts with the total.

Try it yourself

Type the data in, then write formulas for: revenue for region S, units of Books, the count of rows with revenue above 30 and the revenue for region S Books only. Confirm the last one is 75 (50 + 25).

Summary

SUMIF, COUNTIF and AVERAGEIF handle one condition, and the IFS versions handle several.
In SUMIFS the range to add comes first.
Check results by making sure the parts add up to the total and trying small examples.