Excel practice › Excel functions › COUNTIF

COUNTIF in Excel: count the cells that meet one condition

COUNTIF counts the cells in a range that meet one condition. With invoice statuses in C2:C8, the number of open invoices is =COUNTIF(C2:C8,"Open").

The condition can be a text, a number, a comparison like "<500", a pattern with wildcards or a cell. Upper and lower case make no difference.

Skip to the 3 exercises ↓

Syntax

=COUNTIF(range, criteria)

range
The cells to look through.
criteria
What to count: a number (480), a text in quotes ("Open"), a comparison in quotes (">=1000") or a cell (F2). One condition only; for more, there is COUNTIFS.
Live exampleInvoices with the status in F2
G2fx
ABCDEFG
1InvoiceClientStatusAmountStatusCount
2INV-1041Harbor DentalPaid480Paid3
3INV-1042Lakeside Auto LLCOpen1250
4INV-1043Bright Path TutoringPaid300
5INV-1044Summit Roofing LLCOverdue2900
6INV-1045Harbor DentalOpen480
7INV-1042Lakeside Auto LLCOpen1250
8INV-1046Cedar Street CafePaid175

Numbers: the operator goes in quotes

To count numbers above or below a limit, write the operator and the number together, inside double quotes. An exact number needs no quotes: =COUNTIF(D2:D8,480) is 2 on this list.

Live exampleInvoices under $500
F2fx
ABCDEF
1InvoiceClientStatusAmountUnder 500
2INV-1041Harbor DentalPaid4804
3INV-1042Lakeside Auto LLCOpen1250
4INV-1043Bright Path TutoringPaid300
5INV-1044Summit Roofing LLCOverdue2900
6INV-1045Harbor DentalOpen480
7INV-1042Lakeside Auto LLCOpen1250
8INV-1046Cedar Street CafePaid175

Wildcards: part of a text

* stands for any run of characters, ? for exactly one. On this list, =COUNTIF(B2:B8,"*LLC") counts the clients whose name ends in LLC (3 rows), "Harbor*" the names that start with Harbor (2), and "*Auto*" the names with Auto anywhere in them (2). To count a real asterisk or question mark, put a tilde before it: "~?".

Cells that aren't blank

To count the cells that have anything in them, the function is COUNTA: =COUNTA(A2:A8) is 7 here, one per invoice. It counts every cell that isn't empty, whatever it holds. =COUNTIF(range,"*") is narrower: Microsoft documents it as counting the cells that contain text.

A count that comes up short: spaces you can't see

COUNTIF compares the whole content of each cell. A status typed as Open with a space after it looks identical on screen, and it is a different text. Microsoft's page names leading and trailing spaces as a cause of unexpected counts. One of the three Open cells below has one:

Live example2 instead of 3: a trailing space
G2fx
ABCDEFG
1InvoiceClientStatusAmountStatusCount
2INV-1041Harbor DentalPaid480Open2
3INV-1042Lakeside Auto LLCOpen1250
4INV-1043Bright Path TutoringPaid300
5INV-1044Summit Roofing LLCOverdue2900
6INV-1045Harbor DentalOpen 480
7INV-1042Lakeside Auto LLCOpen1250
8INV-1046Cedar Street CafePaid175

Check for duplicates

Count how many times a row's own value appears in the whole column. 1 means it is unique, 2 or more means it is repeated. The range is locked with $ so the formula can be copied down the column without the range sliding.

Live exampleHow many times is this invoice number listed?
E3fx
ABCDE
1InvoiceClientStatusAmountTimes listed
2INV-1041Harbor DentalPaid480
3INV-1042Lakeside Auto LLCOpen12502
4INV-1043Bright Path TutoringPaid300
5INV-1044Summit Roofing LLCOverdue2900
6INV-1045Harbor DentalOpen480
7INV-1042Lakeside Auto LLCOpen1250
8INV-1046Cedar Street CafePaid175

Practice: 3 exercises

Type the formula in the box, press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data, so a typed-in number won't pass.

Exercise 1Count one client's invoices

F2 holds a client name. In G2, count how many invoices that client has.

G2fx
ABCDEFG
1InvoiceClientStatusAmountClientInvoices
2INV-1041Harbor DentalPaid480Harbor Dental
3INV-1042Lakeside Auto LLCOpen1250
4INV-1043Bright Path TutoringPaid300
5INV-1044Summit Roofing LLCOverdue2900
6INV-1045Harbor DentalOpen480
7INV-1042Lakeside Auto LLCOpen1250
8INV-1046Cedar Street CafePaid175
Show the answer

=COUNTIF(B2:B8,F2) gives 2.

COUNTIF goes down B2:B8 and counts the cells equal to the text in F2. Harbor Dental is on rows 2 and 6.

Exercise 2Above a limit kept in a cell

F2 holds an amount. In G2, count the invoices for more than that amount.

G2fx
ABCDEFG
1InvoiceClientStatusAmountOverCount
2INV-1041Harbor DentalPaid480480
3INV-1042Lakeside Auto LLCOpen1250
4INV-1043Bright Path TutoringPaid300
5INV-1044Summit Roofing LLCOverdue2900
6INV-1045Harbor DentalOpen480
7INV-1042Lakeside Auto LLCOpen1250
8INV-1046Cedar Street CafePaid175
Show the answer

=COUNTIF(D2:D8,">"&F2) gives 3.

">"&F2 builds the condition >480. Three amounts are above it: 1250, 2900 and 1250. The two invoices of exactly 480 are not.

Exercise 3Flag a duplicate

An invoice number should be in the list once. In E7, write a formula that gives TRUE when the number in A7 appears more than once in A2:A8, and FALSE when it doesn't.

E7fx
ABCDE
1InvoiceClientStatusAmountListed twice?
2INV-1041Harbor DentalPaid480
3INV-1042Lakeside Auto LLCOpen1250
4INV-1043Bright Path TutoringPaid300
5INV-1044Summit Roofing LLCOverdue2900
6INV-1045Harbor DentalOpen480
7INV-1042Lakeside Auto LLCOpen1250
8INV-1046Cedar Street CafePaid175
Show the answer

=COUNTIF(A2:A8,A7)>1 gives TRUE.

COUNTIF finds INV-1042 twice, on rows 3 and 7, and 2 > 1 is TRUE. On a unique number the count is 1 and the result is FALSE. With the range written $A$2:$A$8, the formula can be copied down the whole column.

Questions people ask

Is COUNTIF case sensitive?

No. "open", "Open" and "OPEN" count the same cells.

How do I count cells that contain one of two values?

COUNTIF takes one condition, so add two of them: =COUNTIF(C2:C8,"Paid")+COUNTIF(C2:C8,"Overdue") gives 4 on the list above.

Why does COUNTIF give 0 or the wrong count?

Check the condition first: text and comparisons need double quotes, and a cell reference has to stay outside them (">"&F2, not ">F2"). Then check the data: a space before or after the text makes a cell different from the one you're counting. The third live example on this page is that case.

How do I use COUNTIF with two conditions?

Use COUNTIFS. It takes pairs of range and condition and counts the rows that meet all of them, which also covers "between two numbers" and "between two dates".

Keep going

Lesson 5 is seven drills on a sales log: COUNTIF, SUMIF, SUMIFS, COUNTIFS and a conditional average, opening with a COUNTIF warm-up. It's part of Pro ($29 once for everything, or $19 for the Excel track). Lessons 1 and 2 and the level test are free.

Related

COUNTIFS function, with live examplesSUMIF function, with live examplesTRIM function, with live examplesUNIQUE function, with live examplesIF function, with live examplesSUMIFS and COUNTIFS practice (Pro)Excel functions with live examplesExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: Use the COUNTIF function in Microsoft Excel, Microsoft Support: COUNTA function. Last checked November 2026.