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.
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.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Client | Status | Amount | Status | Count | |
| 2 | INV-1041 | Harbor Dental | Paid | 480 | Paid | 3 | |
| 3 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |||
| 4 | INV-1043 | Bright Path Tutoring | Paid | 300 | |||
| 5 | INV-1044 | Summit Roofing LLC | Overdue | 2900 | |||
| 6 | INV-1045 | Harbor Dental | Open | 480 | |||
| 7 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |||
| 8 | INV-1046 | Cedar Street Cafe | Paid | 175 |
F2 with "paid" and it still counts 3. With "Overdue" the count is 1.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.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Invoice | Client | Status | Amount | Under 500 | |
| 2 | INV-1041 | Harbor Dental | Paid | 480 | 4 | |
| 3 | INV-1042 | Lakeside Auto LLC | Open | 1250 | ||
| 4 | INV-1043 | Bright Path Tutoring | Paid | 300 | ||
| 5 | INV-1044 | Summit Roofing LLC | Overdue | 2900 | ||
| 6 | INV-1045 | Harbor Dental | Open | 480 | ||
| 7 | INV-1042 | Lakeside Auto LLC | Open | 1250 | ||
| 8 | INV-1046 | Cedar Street Cafe | Paid | 175 |
&, as in "<"&H1.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:
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Client | Status | Amount | Status | Count | |
| 2 | INV-1041 | Harbor Dental | Paid | 480 | Open | 2 | |
| 3 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |||
| 4 | INV-1043 | Bright Path Tutoring | Paid | 300 | |||
| 5 | INV-1044 | Summit Roofing LLC | Overdue | 2900 | |||
| 6 | INV-1045 | Harbor Dental | Open | 480 | |||
| 7 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |||
| 8 | INV-1046 | Cedar Street Cafe | Paid | 175 |
=LEN(C6): 5 characters, not 4. Counting with "Open*" finds all 3, but the lasting fix is to clean the column with TRIM.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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Client | Status | Amount | Times listed |
| 2 | INV-1041 | Harbor Dental | Paid | 480 | |
| 3 | INV-1042 | Lakeside Auto LLC | Open | 1250 | 2 |
| 4 | INV-1043 | Bright Path Tutoring | Paid | 300 | |
| 5 | INV-1044 | Summit Roofing LLC | Overdue | 2900 | |
| 6 | INV-1045 | Harbor Dental | Open | 480 | |
| 7 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |
| 8 | INV-1046 | Cedar Street Cafe | Paid | 175 |
A3 to A2 and you get 1: that invoice is unique. For a TRUE/FALSE flag, compare the count: =COUNTIF($A$2:$A$8,A3)>1.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.
F2 holds a client name. In G2, count how many invoices that client has.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Client | Status | Amount | Client | Invoices | |
| 2 | INV-1041 | Harbor Dental | Paid | 480 | Harbor Dental | ||
| 3 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |||
| 4 | INV-1043 | Bright Path Tutoring | Paid | 300 | |||
| 5 | INV-1044 | Summit Roofing LLC | Overdue | 2900 | |||
| 6 | INV-1045 | Harbor Dental | Open | 480 | |||
| 7 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |||
| 8 | INV-1046 | Cedar Street Cafe | Paid | 175 |
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.
F2 holds an amount. In G2, count the invoices for more than that amount.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Invoice | Client | Status | Amount | Over | Count | |
| 2 | INV-1041 | Harbor Dental | Paid | 480 | 480 | ||
| 3 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |||
| 4 | INV-1043 | Bright Path Tutoring | Paid | 300 | |||
| 5 | INV-1044 | Summit Roofing LLC | Overdue | 2900 | |||
| 6 | INV-1045 | Harbor Dental | Open | 480 | |||
| 7 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |||
| 8 | INV-1046 | Cedar Street Cafe | Paid | 175 |
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.
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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Invoice | Client | Status | Amount | Listed twice? |
| 2 | INV-1041 | Harbor Dental | Paid | 480 | |
| 3 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |
| 4 | INV-1043 | Bright Path Tutoring | Paid | 300 | |
| 5 | INV-1044 | Summit Roofing LLC | Overdue | 2900 | |
| 6 | INV-1045 | Harbor Dental | Open | 480 | |
| 7 | INV-1042 | Lakeside Auto LLC | Open | 1250 | |
| 8 | INV-1046 | Cedar Street Cafe | Paid | 175 |
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".