IF, SUMIF and COUNTIF

01The problem

Filtering answers one question at a time. But the sales director wants a table: revenue by product, lines by month, discounted lines this year. Rebuilding filters for every cell would take all day. Conditional functions calculate totals and counts for rows that meet a condition, directly in a formula.

02The concept

Lesson 4's functions work on every row: SUM(Orders[revenue]) adds all 4,266 lines. The functions in this lesson add a condition: "add up revenue, but only for product 1", "count the lines, but only the discounted ones". They come in two families:

• IF and friends decide something for one row: "is this line large or small?"
• SUMIFS, COUNTIFS and friends summarise many rows that pass a test: "how much did large lines bring in?"

Conditions: the building block

A condition (also called a logical test) is a comparison that is either TRUE or FALSE. Type these into any cell to see:

Text in a condition goes in double quotes: =C2="Lagos". Text comparisons in Excel ignore upper and lower case, so "lagos" matches Lagos.

IF

Returns one value when a condition is true and another when it's false.

=IF(logical_test, value_if_true, value_if_false)

In the Orders table, a new column size:

=IF([@quantity]>=20, "Large", "Small")

Row 2 has 14 packs, so it shows Small. Row 5 has 28 packs, so it shows Large. Across the whole table, 1,032 lines are Large and 3,234 are Small.

The results don't have to be text. They can be numbers or other formulas:

=IF([@discount_pct]>0, [@quantity]*[@unit_price]-[@revenue], 0)

That's the naira given away in discount on each line, or 0 when there was no discount.

AND, OR and NOT

To test more than one thing, put the tests inside AND or OR:

On Kolanut's orders:

• AND(quantity>=20, discount_pct=0): large lines sold at full price. 374 lines.
• OR(quantity>=20, discount_pct>0): lines that are large, discounted or both. 2,121 lines.

Put either one inside IF:

=IF(AND([@quantity]>=20, [@discount_pct]=0), "Large, full price", "Other")

IFS: more than two outcomes

IF gives two answers. For three or more, you could put an IF inside an IF (a nested IF):

=IF([@quantity]>=20, "Large", IF([@quantity]>=10, "Medium", "Small"))

That works, but each extra level makes it harder to read. IFS lists the tests in order instead:

=IFS(test1, value1, test2, value2, ..., TRUE, value_otherwise)
=IFS([@quantity]>=20, "Large", [@quantity]>=10, "Medium", TRUE, "Small")

Excel checks the tests from left to right and stops at the first true one. So a line of 25 packs passes the first test and becomes "Large"; it never reaches the second. The final TRUE is a catch-all: if nothing else matched, use this.

The bands add up to 4,266, every line exactly once. Checking that is a good habit: it proves no line fell through a gap.

IFS is in Excel 2019 and later, and in Google Sheets. In older Excel, use the nested IF.

COUNTIF and SUMIF: one condition

Now for the summarising family. COUNTIF counts the cells in a range that meet a condition.

=COUNTIF(range, criteria)

=COUNTIF(Orders[discount_pct], ">0")     → 1,747 discounted lines
=COUNTIF(Orders[quantity], ">=20")       → 1,032 large lines
=COUNTIF(Orders[size], "Large")          → 1,032, counting the size column instead

SUMIF adds up the cells in another column for the rows that meet the condition.

=SUMIF(range, criteria, [sum_range])

=SUMIF(Orders[product_id], 1, Orders[revenue])       → 59,804,940 from product 1
=SUMIF(Orders[discount_pct], ">0", Orders[revenue])  → 416,170,045 from discounted lines

Notice how the criteria are written: a plain value (1, "Large") means "equal to"; a comparison goes inside the quotes with the number: ">0", ">=20", "<>0".

SUMIFS and COUNTIFS: several conditions

The -IFS versions (with an S) take as many conditions as you like, in pairs: a range to test, then its condition. A row is included only if it passes all of them.

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

=SUMIFS(Orders[revenue], Orders[product_id], 1, Orders[discount_pct], ">0")
→ 27,877,340: product 1 lines that were discounted

=COUNTIFS(Orders[quantity], ">=20", Orders[discount_pct], 0)
→ 374: large lines at full price, the same answer as the AND test above

COUNTIFS and SUMIFS only do AND (all conditions true). For OR, add two results together, for example =COUNTIF(..., "Lagos") + COUNTIF(..., "Abuja"), taking care not to count any row twice.

AVERAGEIFS, MAXIFS and MINIFS

The same pattern works for averages, largest and smallest values:

The averages tell a story: discounted lines are much bigger than full-price ones (₦238k against ₦164k). That fits a business that gives discounts to customers buying in bulk. You found that with two formulas.

Conditions on dates

To test a date, join the comparison to a date with &. DATE(year, month, day) builds the date, which avoids any confusion between day-month and month-day formats.

=SUMIFS(Orders[revenue], Orders[order_date], ">="&DATE(2025,10,1), Orders[order_date], "<="&DATE(2025,12,31))
→ 160,799,625: October to December 2025

Two conditions on the same column give you a range: on or after 1 October and on or before 31 December.

The same & trick works with a cell: ">="&K2 means "greater than or equal to whatever is in K2". That lets you change a report's dates without touching the formula.

Wildcards for text

In criteria, * stands for "any characters" and ? for "exactly one character". The Customers table has names such as Peace Mart and Grace Provisions:

03Example

Revenue for every product, in one formula. Instead of typing each product ID into the formula, put the conditions in cells:

1. On a new sheet, type product_id in A1 and the numbers 1 to 16 in A2:A17.

2. In B1 type revenue, and in B2:

   Excel formula

   =SUMIFS(Orders[revenue], Orders[product_id], A2)

   Copy

3. Copy B2 down to B17. Each row tests against its own product ID, because A2 is relative: it becomes A3, A4, and so on.

4. In C1 type lines, and in C2: =COUNTIFS(Orders[product_id], A2). Copy it down.

Product 1 shows ₦59,804,940 from 308 lines. A total under the table, =SUM(B2:B17), should be exactly ₦830,541,245: every line belongs to one product, so the products must add back up to the grand total.

This is a small version of a pivot table (lesson 8). Building it with formulas first shows you what a pivot table does underneath.

04Walkthrough

Work through these in your Orders table. Each step builds on the last.

1. Classify each line. Add a column size:

   Excel formula

   =IFS([@quantity]>=20, "Large", [@quantity]>=10, "Medium", TRUE, "Small")

   Copy

2. Count each band. Below or beside the table, type Large, Medium and Small in three cells, say K2:K4. In L2: =COUNTIF(Orders[size], K2), and copy down. You should get 1,032, 1,777 and 1,457.

3. Check the total. =SUM(L2:L4) must be 4,266. If it isn't, a line fell through a gap in your IFS.

4. Revenue per band. In M2: =SUMIFS(Orders[revenue], Orders[size], K2), copied down. Large lines bring in ₦361,021,385, more than 43% of all revenue from less than a quarter of the lines.

5. Product 1 revenue: =SUMIF(Orders[product_id], 1, Orders[revenue]), which should be 59,804,940.

6. Discounted lines in 2026:

   Excel formula

   =COUNTIFS(Orders[order_date], ">="&DATE(2026,1,1), Orders[discount_pct], ">0")

   Copy

7. Check step 6 another way. Filter the table: order_date in 2026, discount_pct not 0. The status bar count should match your formula. Two methods agreeing is the best evidence you're right.

Summary

05Practice

06Challenge

07More practice

Optional drills. They don't count towards the certificate, but they're the fastest way to make this lesson stick. Several use a different dataset from the lesson on purpose: if you can do the same thing on unfamiliar data, you've really learned it.

08Check your understanding