COUNTIFS in Excel: Count With Multiple Criteria, Plus COUNT and COUNTA

COUNT, COUNTA, COUNTIF and COUNTIFS all count cells, but each counts something different. Here is when to use each one, with formulas you can paste into your own sheet.

Updated 18 Nov 2026 · By Softotech Solutions

The short answer

Use COUNTIFS when you need to count rows that meet two or more conditions at once, for example orders that are both “East” and “Paid”. The syntax is =COUNTIFS(range1, criteria1, range2, criteria2, ...). Every range must be the same size, and a row is counted only when all conditions are true.

COUNT, COUNTA, COUNTBLANK, COUNTIF and COUNTIFS compared

These five functions are easy to mix up. This table shows what each one counts.

FunctionWhat it countsExample
COUNTCells that contain numbers=COUNT(B2:B20)
COUNTACells that are not empty (numbers, text, errors)=COUNTA(A2:A20)
COUNTBLANKEmpty cells=COUNTBLANK(C2:C20)
COUNTIFCells that meet one condition=COUNTIF(A2:A20,"East")
COUNTIFSRows that meet several conditions=COUNTIFS(A2:A20,"East",B2:B20,"Paid")

COUNT ignores text, so a column of numbers stored as text returns 0. If that happens, see our guide on fixing numbers stored as text in the VLOOKUP not working checklist.

COUNTIFS examples you can copy

Assume region is in A2:A20, status is in B2:B20, amount is in C2:C20 and the order date is in D2:D20.

Two text conditions: =COUNTIFS(A2:A20,"East",B2:B20,"Paid") counts paid orders in the East region.

Text plus a number test: =COUNTIFS(A2:A20,"East",C2:C20,">=1000") counts East orders of 1,000 or more.

A date range: =COUNTIFS(D2:D20,">="&DATE(2026,1,1),D2:D20,"<="&DATE(2026,3,31)) counts orders placed in the first quarter of 2026. Note the ampersand that joins the operator to the date.

Cell reference as criteria: =COUNTIFS(A2:A20,F2,B2:B20,G2) lets you change the conditions by editing F2 and G2.

Not equal to: =COUNTIFS(B2:B20,"<>Paid") counts every status except Paid.

Wildcards and counting cells with text

In criteria, * stands for any number of characters and ? for exactly one. =COUNTIF(A2:A20,"*") counts every cell that contains text, which answers the common question of how to count cells with text. =COUNTIF(A2:A20,"*invoice*") counts cells that contain the word invoice anywhere. To count text cells that are not blank strings use =COUNTIF(A2:A20,"?*").

Count OR conditions

COUNTIFS joins conditions with AND. To count rows that are East or West, add two counts: =COUNTIFS(A2:A20,"East")+COUNTIFS(A2:A20,"West"). A shorter version is =SUM(COUNTIFS(A2:A20,{"East","West"})), which runs the count once for each item in the curly brackets and adds the results.

How to count characters in Excel

COUNTIF counts cells, not characters. To count characters use LEN. =LEN(A2) returns the number of characters in one cell, including spaces. To count characters across a range use =SUMPRODUCT(LEN(A2:A20)). To count how many times one letter appears in a cell use =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")). This formula is case-sensitive, so wrap the cell in LOWER() for a case-insensitive count.

Common COUNTIFS problems

The result is 0 but you can see matches. The cells may contain extra spaces or numbers stored as text. Clean them with TRIM or convert the text to numbers.

#VALUE! error. The ranges are different sizes. Make every range cover the same number of rows.

Wrong count with dates. Dates are numbers. Use >= and <= with DATE() and the ampersand, not text such as “Jan 1”.

Criteria with a comparison operator. Wrap the operator and value in quotes, as in ">100". To use a cell value, join with an ampersand: ">"&F2.

For summing instead of counting see SUMIFS in Excel, and for the single-condition version read COUNTIF in Excel.

Worked example: a small order sheet

Imagine an order sheet with 200 rows: region in column A, status in column B, amount in column C and order date in column D. These formulas answer the questions a manager usually asks, and each one needs only COUNTIFS or one of its relatives.

QuestionFormula
How many East orders are still open?=COUNTIFS(A2:A201,"East",B2:B201,"Open")
How many orders are between 500 and 1,000?=COUNTIFS(C2:C201,">=500",C2:C201,"<=1000")
How many orders were placed this month?=COUNTIFS(D2:D201,">="&EOMONTH(TODAY(),-1)+1,D2:D201,"<="&EOMONTH(TODAY(),0))
How many rows are not blank in column A?=COUNTA(A2:A201)
How many rows are blank in column B?=COUNTBLANK(B2:B201)

The same pattern works for attendance (count the days a person was “Present”), inventory (count items below a reorder level with "<"&F2) and support tickets (count tickets by priority and owner).

Count unique values and duplicates

To count how many times each value appears, put =COUNTIF($A$2:$A$201,A2) in a helper column. Any result above 1 is a duplicate. To count distinct values in Microsoft 365 use =COUNTA(UNIQUE(A2:A201)). In older versions use =SUMPRODUCT(1/COUNTIF(A2:A201,A2:A201)), which assumes no blank cells in the range. See our guide to removing duplicates in Excel when you want to delete repeats instead of counting them.

COUNTA, blank strings and hidden surprises

COUNTA counts a cell that contains a formula returning an empty string (=""), because the cell is technically not empty. If your count looks too high, that is the usual reason. Use =COUNTIF(A2:A201,"?*") to count only cells with at least one character, or =SUMPRODUCT(--(LEN(A2:A201)>0)). COUNTBLANK, on the other hand, counts both truly empty cells and cells that hold an empty string.

Remember that COUNTIF and COUNTIFS are not case-sensitive, so “paid” and “PAID” are the same. For a case-sensitive count use =SUMPRODUCT(--EXACT(B2:B201,"Paid")).

Speed and accuracy tips

Use whole-column references only on small files, because COUNTIFS over very large ranges slows a workbook. Convert your data to an Excel Table (Ctrl+T) so formulas expand automatically: =COUNTIFS(Orders[Region],"East",Orders[Status],"Paid"). Keep criteria in cells rather than typing them inside formulas so that anyone can change them without editing the formula, and check totals by comparing the count with a filtered row count at the bottom of the screen.

Frequently asked questions

What is the difference between COUNTIF and COUNTIFS?

COUNTIF tests one condition on one range. COUNTIFS tests several conditions on several ranges and counts only rows where all of them are true.

What does COUNTA do in Excel?

COUNTA counts every cell that is not empty, including text, numbers and errors. COUNT counts numbers only.

How do I count cells with text in Excel?

Use =COUNTIF(range,”*”) to count cells that contain text.

How do I count characters in a cell?

Use =LEN(A2). To count across a range use =SUMPRODUCT(LEN(A2:A20)).

Can COUNTIFS use OR logic?

Not directly. Add separate COUNTIFS results, or use =SUM(COUNTIFS(range,{“East”,”West”})).

Official reference: COUNTIFS function (Microsoft Support)

Need Excel work done for you?

Talk to us

More free tools: COUNTIF in Excel · SUMIFS in Excel · IF formula in Excel · All free calculators

Guides: How to Use the AND and OR Functions in Excel · How to Use COUNTIF in Excel · How to Rank in Excel