IntermediateLesson 12 of 305 min read

Counting and adding with conditions: COUNTIF, SUMIF and friends

Answer business questions with COUNTIF, SUMIF, AVERAGEIF and their multi-condition versions, with real, independently checked results.

In this lesson you will

  • Use COUNTIF, SUMIF and AVERAGEIF
  • Use COUNTIFS and SUMIFS with several conditions
  • Write conditions with operators, wildcards and cells
  • Total by month

About 15 minutes

The question every report asks

Anita manages a small stationery business and has a sheet with twelve sales: date, region, product, units, price and revenue. Her owner keeps asking questions: ‘How much did North sell? How many notebook sales in the East? What was the average North sale? What did we make in March?’ She could filter and read off totals, but then she must redo it for every new question. The family of IF functions answers each one with a single formula that updates by itself.

The six functions

Function Job
COUNTIF Counts cells that meet one condition
SUMIF Adds values where a condition is met
AVERAGEIF Averages values where a condition is met
COUNTIFS Counts with several conditions
SUMIFS Adds with several conditions
AVERAGEIFS Averages with several conditions

The pattern

For the singular versions the order is range to test, condition, range to add. For example =SUMIF(B2:B13,“North”,F2:F13) looks down the Region column for North and adds the matching cells of the Revenue column. For the plural versions the sum range comes first, then pairs of range and condition: =SUMIFS(F2:F13,B2:B13,“South”,C2:C13,“Notebook”). This difference in order catches many people out, so say the pattern aloud: ‘add the revenue where region is South and product is Notebook.’

Real results

The twelve sales have total revenue of 4160. These formulas were really calculated on the sheet and then checked with an independent Python calculation, and all of them matched:

Formula Question Result
=COUNTIF(B2:B13,“North”) Sales rows in North 4
=SUMIF(B2:B13,“North”,F2:F13) North revenue 1340
=AVERAGEIF(B2:B13,“North”,F2:F13) Average North sale 335
=COUNTIFS(B2:B13,“East”,C2:C13,“Notebook”) East notebook sales 2
=SUMIFS(F2:F13,B2:B13,“South”,C2:C13,“Notebook”) South notebook revenue 540
=SUMIF(D2:D13,“>=10″,F2:F13) Revenue of sales with 10+ units 1640
=COUNTIF(F2:F13,“>”&500) Sales above 500 1
=COUNTIF(B2:B13,“*th”) Sales rows with a region ending th 7

Writing conditions

Want Condition
Exactly North “North” or a cell like H2
Not North “<>North”
Greater than 500 “>500”
Greater than a cell value “>”&H2
Begins with N “N*”
Contains no “*no*”

The wildcard * stands for any run of characters and ? for any single character. In the test, =COUNTIF(B2:B13,“*th”) counted the sales rows whose region ends in ‘th’ (the North and South rows). When you join an operator with a cell, use the ampersand, as in “>=”&H2.

Totals by month

Dates need two conditions, a start and an end. For March 2026 the formula was =SUMIFS(F2:F13,A2:A13,“>=“&DATE(2026,3,1),A2:A13,“<“&DATE(2026,4,1)), which gave 1940. Using ‘less than the first of next month’ avoids problems with the last day and with times.

Better than typing the word

Put the region name in a cell, such as H2, and write =SUMIF(B2:B13,H2,F2:F13). Now one formula serves any region you type. Even better, list all the regions and copy the formula down: instantly you have a mini-report. Lock the ranges with dollar signs, such as $B$2:$B$13, so copying does not slide them (lesson 6).

Common problems

  • Ranges of different sizes in SUMIFS cause #VALUE!. Make them the same length.
  • A condition that looks right but returns 0: check for extra spaces or numbers stored as text.
  • Quotes are needed around text and operators; cell references are not quoted.
  • Whole-column ranges such as B:B are fine and grow with the data, but keep them out of the column you are summing.

Practice

Use a list of at least twelve rows of your own (expenses by category, sales by city, marks by class). Build a small summary table: one row per category with a count, a total and an average, all using one formula each with the category read from a cell. Then add a second condition, such as a month or a minimum amount, using the IFS versions.

Practice answer

Check that the totals in your summary add up to the total of the whole list. If the summary total is smaller, a category is spelled slightly differently in the data or has a trailing space. Use TRIM or Find and Replace to fix it.

Common mistake

Mixing up the argument order. SUMIF puts the range to add last, but SUMIFS puts it first. If your SUMIFS returns 0 or an error, check the order.

Building a mini report from one formula

List the regions down column H: North, South, East and West. In column I write =SUMIF($B$2:$B$13,H2,$F$2:$F$13) and copy it down. In column J write =COUNTIF($B$2:$B$13,H2), and in column K =I2/J2 for the average per sale. In four rows you have a small report that updates whenever the sales list changes. Add a total row with SUM and check that it equals the total of the whole list: that single comparison catches misspelled categories and stray spaces before anyone else sees the report.

When the IF family is not enough

These functions answer questions about totals, counts and averages. If you need the biggest sale in one region, use MAXIFS (Excel 2019 and later). If you need a table of results by two things at once, such as region by month, a PivotTable (lesson 18) does it without any formulas. Knowing when to switch tools is part of working like a professional.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

Which function adds values that meet one condition?

  1. COUNT
  2. SUMIF
  3. ROUND
  4. LEN

SUMIF adds matching rows.

In SUMIFS, which argument comes first?

  1. The condition
  2. The criteria range
  3. The range to add
  4. The result cell

SUMIFS starts with the sum range.

What does the * wildcard mean?

  1. Any run of characters
  2. Multiply
  3. Exactly one letter
  4. A date

N* matches North.

How do you write ‘greater than the value in H2’?

  1. “>H2”
  2. >H2
  3. “>”&H2
  4. H2>

Join the operator and cell with an ampersand.

Why use a cell for the condition instead of typing the word?

  1. One formula works for any value
  2. It is faster to type
  3. Excel requires it
  4. It changes colours

Change the cell and the answer follows.

Summary

  • The IF family answers ‘how many, how much, what average’ with conditions.
  • SUMIF puts the sum range last; SUMIFS puts it first.
  • Write conditions with quotes, operators, wildcards and cell links.
  • Use start and end dates for monthly totals.

Next lesson

Next: lookups, finding a value in a table.

Questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one condition. COUNTIFS takes several pairs of range and condition and counts rows meeting all of them.

Can SUMIF use OR logic?

Add two SUMIFs together, or use SUM with an array of criteria such as SUM(SUMIF(B2:B13,{"North","South"},F2:F13)).

Do these work in Google Sheets?

Yes. The IF family has the same names and argument order in Google Sheets.

Progress is saved in this browser.