COUNTIF and COUNTIFS in Excel - Step-by-Step Guide

Introduction

COUNTIF and COUNTIFS help you count how many times certain things happen in your data. COUNTIF counts based on one rule, while COUNTIFS counts based on multiple rules at the same time. This is useful when you want to know how many items meet your conditions.

When you want to count how many sales are above a certain number.

When you need to find how many students scored more than 80 in math.

When you want to count how many orders came from a specific city.

When you want to count how many products are in stock and cost less than $20.

When you want to count how many employees work in a department and have more than 5 years of experience.

Steps

Step 1: Click

- cell where you want the result

The cell is selected and ready for input

Step 2: Type

- the selected cell

Formula starts to appear in the formula bar

💡 Start typing =COUNTIF( for one condition or =COUNTIFS( for multiple conditions

Step 3: Enter

- the range of cells you want to check, for example A2:A10

The range is shown inside the formula

Step 4: Type

- the condition in quotes, for example ">10" or "=Apple"

The condition is added to the formula

Step 5: For COUNTIFS only, repeat

- add more range and condition pairs separated by commas

Multiple conditions are added to the formula

Step 6: Press Enter

- the formula bar

The cell shows the count of items matching the condition(s)

Before vs After

Before

A list of sales amounts in cells A2 to A10 with values like 5, 12, 7, 20, 15

After

A cell shows the number 4 because four sales are greater than 10 using =COUNTIF(A2:A10, ">10")

Settings Reference

Range

📍 Inside the COUNTIF or COUNTIFS formula

Defines which cells to check for the condition

Default: No default, must specify

Criteria

📍 Inside the COUNTIF or COUNTIFS formula after the range

Defines the rule to count cells that meet it

Default: No default, must specify

Common Mistakes

Not putting the condition in quotes

Excel needs the condition as text to understand it, like ">10" not >10

Always put conditions inside double quotes, for example ">10" or "=Apple"

Using COUNTIF when multiple conditions are needed

COUNTIF only checks one condition, so it cannot count based on multiple rules

Use COUNTIFS when you want to count with more than one condition

Mixing ranges of different sizes in COUNTIFS

All ranges in COUNTIFS must be the same size or Excel will give an error

Make sure all ranges in COUNTIFS cover the same number of rows or columns

Summary

COUNTIF counts cells that meet one condition; COUNTIFS counts cells that meet multiple conditions.

Conditions must be written inside quotes, like ">10" or "Text".

All ranges in COUNTIFS must be the same size to work correctly.