Excel
Word
PowerPoint
Outlook
Google Sheets
Google Docs
Google Slides

Learning Hub › Formulas › Count

Count Cells Equal to a Value in Excel and Google Sheets

Count 📊 All versions ✓ Google Sheets Same syntax in both apps

Purpose

Counting how many times a specific value appears in a column is one of those everyday spreadsheet tasks I do…

Counting how many times a specific value appears in a column is one of those everyday spreadsheet tasks I do without thinking now — but it took me years of fighting with filters and pivot tables before I realised the COUNTIF function handles it in one formula. How many orders were marked Approved? How many students scored exactly 85? How many times does the word Pending appear in a status column? These are all the same problem: count the cells in a range that equal a specific value. COUNTIF solves every one of them with the same short formula, and it is the first building block I teach anyone who wants to get beyond basic spreadsheet use. It works in every version of Excel and Google Sheets I have ever touched, and once you are comfortable with it you are one step away from the multi-condition counting handled by the COUNTIFS function. If you understand this formula well you will reach for it every single week.

Syntax

✓ All versions ✓ Google Sheets = Same syntax

=COUNTIF(range, criteria)

Arguments

Argument	Required	Description
range	Required	The cells you want COUNTIF to examine. Can be a column reference like B:B, a specific range like B2:B100, or a named range. Blank cells are ignored.
criteria	Required	The value you want to count. Can be a number, a text string in quotes like "Approved", a cell reference like D1, or an expression like ">=500". Text matching is case-insensitive.

How it works

COUNTIF takes two arguments — the range of cells to examine and the value (criteria) you want to count. It walks through each cell in the range, compares it to the criteria using an exact match, and adds one to a running total every time it finds a match. Text comparisons are case-insensitive, so Approved and APPROVED both count as the same value, which is usually what you want but occasionally trips people up with mixed-case data. Number comparisons match the underlying stored value, not the display format, so a cell showing 1,000 and one showing 1000 both count as the same number as long as the stored value is 1000. Empty cells are ignored entirely. If the criteria does not appear in the range at all, COUNTIF returns 0 rather than an error — it is genuinely hard to break. For counting with more than one condition at the same time, you step up to COUNTIFS, and if you need to count only cells that are not equal to a value, the count cells not equal to a value formula is the mirror image of this one.

Examples

Count how many orders have a status of Approved by searching the Status column for the literal text Approved.

fx =COUNTIF(C2:C11,"Approved")

	A	B	C
1	Order ID	Status	Count Approved
2	ORD-001	Approved	=COUNTIF(C2:C11,"Approved")
3	ORD-002	Pending
4	ORD-003	Approved	4
5	ORD-004	Cancelled
6	ORD-005	Pending
7	ORD-006	Approved
8	ORD-007	Cancelled
9	ORD-008	Pending
10	ORD-009	Approved
11	ORD-010	Pending

Row 2: 4 — Four rows have the status Approved: ORD-001, ORD-003, ORD-006, and ORD-009.

The criteria Approved is wrapped in double quotes because COUNTIF needs text criteria to be quoted. Without the quotes Excel would treat Approved as a cell reference and return a #NAME? error.

Count how many students scored exactly 85 on a test by searching the Score column for the number 85.

fx =COUNTIF(B2:B11,85)

	A	B	C
1	Student	Score	Count of 85
2	Alice	78	=COUNTIF(B2:B11,85)
3	Ben	85
4	Carla	92	4
5	Dan	85
6	Ella	67
7	Finn	85
8	Gia	90
9	Hugo	73
10	Iris	85
11	Jo	88

Row 2: 4 — Ben, Dan, Finn, and Iris all scored exactly 85, giving a count of 4.

Because 85 is a number, no quotes are needed around the criteria. Writing =COUNTIF(B2:B11,"85") also works because COUNTIF coerces the text to a number, but the unquoted version is cleaner.

Use a cell reference for the criteria so the count updates when you change the value in cell A2.

fx =COUNTIF(C2:C11,A2)

	A	B	C	D
1	Criteria	Order ID	Status	Count
2	Cancelled	ORD-001	Approved	=COUNTIF(C2:C11,A2)
3		ORD-002	Pending
4		ORD-003	Approved	2
5		ORD-004	Cancelled
6		ORD-005	Pending
7		ORD-006	Approved
8		ORD-007	Cancelled
9		ORD-008	Pending
10		ORD-009	Approved
11		ORD-010	Pending

Row 2: 2 — Cell A2 contains Cancelled. ORD-004 and ORD-007 are the two rows with that status.

When the criteria is a cell reference, you do not wrap it in quotes. If you change A2 to Approved the formula instantly returns 4, to Pending it returns 4, to something that does not exist it returns 0. This pattern is the foundation of interactive count dashboards.

Common use cases

1. Count how many orders have a specific status such as Approved, Pending, or Cancelled

=COUNTIF(B2:B100,"Approved")

2. Count employees in a specific department by searching a department column

=COUNTIF(C2:C500,"Sales")

3. Count how many students scored exactly the pass mark on a test

=COUNTIF(D2:D200,75)

4. Count how many tickets were assigned to a specific person using a cell reference for flexibility

=COUNTIF(E2:E300,A1)

5. Count product occurrences in a sales log to find best-selling items

=COUNTIF(A2:A1000,"Wireless Mouse")

Common errors

#NAME?

The criteria text is not wrapped in double quotes, so Excel treats it as a named range that does not exist.

Fix: Wrap text criteria in double quotes such as "Approved" or use a cell reference that contains the text. Numbers do not need quotes.

#VALUE!

The range argument is not a valid range — for example, you typed a single cell value where a range was expected.

Fix: Make sure the first argument is a range of cells such as B2:B100 and not a value like B2 or a text string.

Count returns 0 unexpectedly

The criteria does not match any cell exactly. Common causes are trailing spaces in the data, different casing in numeric-as-text values, or a mismatch between numbers and text versions of the same value.

Fix: Use TRIM to clean extra spaces. Check for data type mismatches by testing =COUNTIF(range,"1000") and =COUNTIF(range,1000) separately to see which format your data uses.

Tips and variations

Use a cell reference for the criteria to build dynamic reports

Instead of hard-coding "Approved" into the formula, put the value you want to count in a cell like D1 and reference it in the COUNTIF. Change D1 and the count updates instantly. This is the foundation of every interactive dashboard I build.

=COUNTIF(B2:B100,D1)

Count across an entire column using a column reference

When your data grows over time, you do not have to keep extending the range. Use a whole-column reference like B:B so the formula automatically includes new rows. The small performance cost is worth it for most business-size datasets.

=COUNTIF(B:B,"Approved")

Combine with IF to convert a count into a traffic-light status

Wrap COUNTIF in an IF to show status text based on the count. Any department with zero approved orders, any product with more than ten backorders — these rules become single formulas. This is where COUNTIF bridges into the IF function world and powers most of the conditional reporting in my spreadsheets.

=IF(COUNTIF(B2:B100,"Approved")>=10,"On track","Below target")

Excel vs Google Sheets

⚡

Excel vs Google Sheets

COUNTIF works identically in Excel 2003 and newer, Excel 365, and every version of Google Sheets. The syntax, argument behaviour, and case-insensitive text matching are the same in both applications. Formulas copy between the two without changes.