COUNTIFS Function

Excel Guides / Math & Statistics

Math & Statistics

Count the rows that meet one condition or several, including blanks, partial matches and date windows.

Updated 12 Oct 2026 Ā· 5 min read Ā· By Sky Dashboards

COUNTIFS tells you how many rows meet your conditions. How many bookings are confirmed, how many orders are still unpaid, how many clients booked twice. It works like SUMIFS without the sum range, so learning one teaches you the other.

COUNTIFS syntax

=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Argument Required? What it does
criteria_range1 Required The cells to test.
criteria1 Required The condition those cells must meet, such as "Confirmed", ">=5" or a cell reference.
criteria_range2, criteria2, ... Optional More pairs of range and condition, up to 127 pairs in total.

A row is counted only when it passes every condition. All criteria ranges must have the same number of rows and columns, or COUNTIFS returns #VALUE!. Text conditions ignore case.

Tip

Conditions follow the same rules as in SUMIFS: operators go inside the quotes (">=5"), cell references stay outside and are joined with & (">="&G1).

Count with one condition, then several

Here is a photography studio's booking list for March:

A B C D E
1 Date Client Session Status Deposit paid
2 02-Mar-26 Harper Portrait Confirmed 20-Feb-26
3 04-Mar-26 Nguyen Family Pending
4 07-Mar-26 Okafor Portrait Confirmed 25-Feb-26
5 12-Mar-26 Silva Headshot Cancelled
6 15-Mar-26 Harper Family Confirmed 01-Mar-26
7 21-Mar-26 Patel Headshot Pending 10-Mar-26
8 28-Mar-26 Brooks Portrait Confirmed
9 03-Apr-26 Nguyen Headshot Confirmed 24-Mar-26

Bookings in A1:E9. Column E is empty where no deposit has come in yet.

=COUNTIFS(D2:D9,"Confirmed")

One condition: 5 confirmed bookings.

=COUNTIFS(D2:D9,"Confirmed",C2:C9,"Portrait")

Two conditions: 3 confirmed portrait sessions.

Each extra pair narrows the count further. Swap a typed condition for a cell reference, such as =COUNTIFS(D2:D9,G1), and the same formula answers the question for whatever status you type in G1.

Count blank and non-blank cells

Two special criteria deal with empty cells:

  • "" (two double quotes with nothing between) matches empty cells.
  • "<>" (not equal to nothing) matches cells that are not empty.
=COUNTIFS(D2:D9,"Confirmed",E2:E9,"")

Confirmed bookings with no deposit yet: 1, the Brooks session. That is your chase list.

=COUNTIFS(E2:E9,"<>")

Bookings with a deposit recorded: 5.

Note

A formula that returns "" looks empty, and the "" criterion matches it as well as truly empty cells. When a column mixes real blanks and formula blanks, check the count against a few rows you can verify by eye.

Partial matches with wildcards

Text criteria accept * for any run of characters and ? for exactly one character. Put ~ in front of either to search for the character itself.

  • "N*" matches text that starts with N.
  • "*@gmail.com" matches email addresses that end in @gmail.com.
  • "*refund*" matches notes that mention a refund anywhere.
  • "?????" matches text exactly five characters long.
=COUNTIFS(B2:B9,"N*")

Bookings for clients whose name starts with N: 2, both for Nguyen.

Note

Wildcards only apply to text. A number such as 2026 is not matched by "20*".

Count within a date window

Use the date column twice: once for the start of the window, once for the end.

=COUNTIFS(A2:A9,">="&DATE(2026,3,1),A2:A9,"<="&DATE(2026,3,31))

Bookings dated in March 2026: 7. The 3 April session is left out.

Keep the dates in cells and the window becomes adjustable. With a start date in G1 and an end date in G2:

=COUNTIFS(A2:A9,">="&G1,A2:A9,"<="&G2,D2:D9,"Confirmed")

With 01-Mar-26 and 15-Mar-26, the result is 3: Harper, Okafor and Harper again. The cancelled Silva session on 12 March is left out.

Tip

For a rolling window, such as the last 30 days, use ">="&TODAY()-30 as the condition. The count updates each day the file is opened.

Find and count duplicates

COUNTIF with a locked range and a relative condition tells you how many times each value appears. Copy the client names into a helper list, add COUNTIF next to them and fill down:

B2fx=COUNTIF($A$2:$A$9,A2)
A B
1 Client Times booked
2 Harper 2
3 Nguyen 2
4 Okafor 1
5 Silva 1
6 Harper 2
7 Patel 1
8 Brooks 1
9 Nguyen 2

Every client whose count is above 1 has booked more than once.

Filter the helper column for values greater than 1, or use the same formula in a conditional formatting rule to shade repeat clients.

To flag a duplicate only when two columns match, use COUNTIFS with both. In the booking list, =COUNTIFS($A$2:$A$9,A2,$B$2:$B$9,B2)>1 returns TRUE only when the same client appears twice on the same date.

To count how many different clients there are, in Microsoft 365 or Excel 2021 and later, write =COUNTA(UNIQUE(A2:A9)) on the helper list. It returns 6. The UNIQUE guide has more.

COUNTIF, COUNTA and COUNTBLANK

COUNTIF is the single-condition original: =COUNTIF(range, criteria). COUNTIFS with one pair gives the same answer, and adding a condition later is just two more arguments, so COUNTIFS is the better habit. Unlike SUMIF and SUMIFS, there is no order to trip over: the range always comes before its condition.

Function What it counts
COUNT Cells containing numbers, including dates, which are numbers underneath
COUNTA Cells that are not empty, including text, errors and formulas that return ""
COUNTBLANK Empty cells, plus formulas that return ""
COUNTIF Cells that meet one condition
COUNTIFS Rows that meet every condition

Watch out

A formula returning "" is counted by both COUNTA and COUNTBLANK, so the two can add up to more than the number of cells. If a column is full of IF formulas that return "", count it with COUNTIFS and a real condition instead.

Example: bookings by session and status

A small grid answers most questions about the booking list at once. Sessions go down column G, statuses across row 1, and one COUNTIFS fills every cell.

H2fx=COUNTIFS($C$2:$C$9,$G2,$D$2:$D$9,H$1)
G H I J
1 Session Confirmed Pending Cancelled
2 Portrait 3 0 0
3 Family 1 1 0
4 Headshot 1 1 1
5 Total 5 2 1

H2 counts confirmed portrait sessions. Filled across and down, the grid covers every combination, and the totals add up to the 8 bookings in the list.

$G2 always reads the session from column G and H$1 always reads the status from row 1, so the formula can be filled in both directions. The total row is a quick check: if it does not match the number of bookings, a status is spelt differently somewhere in the data.

Tip

Typos are the most common reason a row goes uncounted. A drop-down list on the Status column keeps every entry spelt the same way.

Frequently asked questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one range and one condition. COUNTIFS takes up to 127 pairs and counts the rows that meet all of them. With a single pair they return the same result.

How do I count cells that are not blank?

Use =COUNTIFS(range,"<>") or =COUNTA(range). If the column holds formulas that return "", check the result, because COUNTA treats those cells as filled.

Can COUNTIFS use OR logic?

Not on its own. Add two COUNTIFS together for options in the same column, or use =SUM(COUNTIFS(D2:D9,{"Confirmed","Pending"})), which returns 7 for the booking list above.

Why does COUNTIFS return #VALUE!?

Either the criteria ranges are different sizes, or the formula points to another workbook that is closed. Make every range cover the same rows, and open any linked file.

Want the finished dashboard instead?

My Excel templates come with these formulas already wired up. Pick one, type in your own rows and the charts update.

Browse templates ā†’

Related guides

All 32 Excel guides