Counting by Criteria With COUNTIF

SUMIF totals matching numbers. Sometimes you do not care about the total, you just want to know how many rows match a condition. How many sales were in the East region? How many scores were above 90?

That is the job of COUNTIF. It scans a range and counts every cell that satisfies your condition, ignoring the rest.

COUNTIF is simpler than SUMIF because it only needs two pieces:

• range the cells to look through
• criteria the condition each cell is tested against

There is no separate sum_range, because COUNTIF counts the matching cells in the same range it tests.

Suppose regions are listed in A2:A10. To find out how many rows are East, test that range for the word East wrapped in quotes.

The spreadsheet reads each cell and adds one to its tally every time it sees East. Just like SUMIF, text matching is not case sensitive.

Hardcoding the word is fine, but pointing at a cell makes the formula flexible. If D1 holds a region name, count matches against D1.

Change D1 to West and the count updates immediately. This pairs nicely with a SUMIF in the next column so you can show both a total and a count for the same region.

COUNTIF works on numbers too. To count how many scores in B2:B10 are exactly 100, use the number itself as the criteria.

No quotes are needed around a plain number, although quotes would still work. Each cell equal to 100 adds one to the result.

The real power shows up with comparison operators inside quotes. To count scores greater than 90:

• ">90" above 90
• "<60" below 60
• ">=70" 70 or higher

This formula tells you how many entries cleared the 90 mark, perfect for a pass or fail count.

To compare against a value stored in a cell, join the operator and the cell with an ampersand, exactly like SUMIF. If D1 holds a cutoff score, count everything at or above it.

The & stitches ">=" to the contents of D1, producing a live condition such as ">=70".

To count cells that are not a certain value, use the not-equal operator <>. This formula counts every region that is not East.

You can also count non-blank cells with "<>" alone, which matches any cell that has something in it. It is a quick way to see how many entries have been filled in.

Two handy patterns:

• "" counts truly empty cells
• "<>" counts cells that contain anything

This is useful for tracking progress, like how many tasks still have no status. Note that a cell with a space looks blank but is not, so clean your data if counts seem off.

COUNTIF shines when building a frequency summary. Put each unique region in cells D2, D3, D4, then write one COUNTIF that points at the label cell and fill it down.

Now each row shows how many times that region appears. Add a SUM under the counts and it should equal the number of data rows, a quick sanity check that nothing slipped through.

A clever use: spot duplicates. If you count how many times the value in A2 appears in the whole column and get more than one, it is a duplicate.

Wrap it in an IF to flag them: a result above 1 means the entry repeats. This is a popular trick for cleaning lists before analysis.

Test your understanding of comparison criteria in COUNTIF.

You can now count rows that meet a condition with COUNTIF. Remember:

• The pattern is just range, criteria with no sum_range.
• Quotes wrap text and operator-based criteria like ">90".
• Join an operator to a cell with &, as in ">="&D1.
• Use it for frequency tables and to flag duplicates.

Next you will average only the matching values with AVERAGEIF.