Day 4 — COUNTIF, SUMIF and the IFS family

Counting and totalling only the rows that match a condition. This is the heart of every MIS report.

COUNTIF

=COUNTIF(range to check, criteria)

Every formula on this page runs against the Sales sheet below. The row numbers and column letters are real — F2 really is 165000.

The criteria goes in quotes Including the operator: ">100000", not >100000. It looks wrong the first few times. Excel treats the whole condition as a piece of text it then interprets.

SUMIF

=SUMIF(range to check, criteria, range to add up)

North sold 256,800. Laptops alone account for 825,000 of the 1,068,000 total.

The argument order trips everyone up COUNTIF is (where to look, what to look for). SUMIF is (where to look, what to look for, what to add up) — the column you are totalling comes last, not first. Get these the wrong way round and you will get a number, just not the right one.

When you leave the third argument out, SUMIF adds up the same range it checked — that is what the last line above does: it totals the amounts that are themselves over 100000.

Criteria can be a cell, not a typed value

Hard-coding "North" inside a formula means editing the formula to change the region. Point at a cell instead:

The last one uses & to glue the operator onto the cell value, giving "everything that is not North". Joining an operator to a reference like this is the standard trick for flexible criteria.

Wildcards

* stands for any number of characters, ? for exactly one.

COUNTIFS and SUMIFS — more than one condition

=COUNTIFS(range1, criteria1, range2, criteria2, ...) =SUMIFS(range to add up, range1, criteria1, ...)

SUMIFS reverses SUMIF In SUMIFS the range you are totalling comes first. In SUMIF it comes last. This is genuinely inconsistent design in Excel itself, and it is the single most common cause of a wrong total in an MIS report.

All the conditions must be true on the same row — the conditions are joined with AND, never OR.

Between two values

There is no BETWEEN in Excel. You give two conditions on the same range:

Dates as criteria

Q1 brought in 484,200 and everything from April onwards 583,800 — which together make the full 1,068,000, a quick way to prove you have not missed any rows.

Always build dates with DATE() Writing ">=01/04/2025" depends on regional settings and will behave differently on a colleague's machine. DATE(2025,4,1) is unambiguous everywhere.

AVERAGEIF

The average laptop order is 137,500 while the average mouse order is 8,100 — the kind of gap that a single overall average completely hides.

Try these yourself

Count how many orders came from the East region.
Total the amount for all Monitor orders.
Count orders where the quantity is 5 or more.
Total the West region amount for Laptops only.
Count the orders placed in February 2025 using two date conditions.

← PreviousDay 3 — IF and logical tests Next →Day 5 — Cleaning text: TRIM, LEFT, RIGHT, MID