How to Use COUNTIFS in Excel: Counting Rows That Meet Several Conditions

Microsoft's definition is that COUNTIFS applies criteria to cells across multiple ranges and counts the number of times all criteria are met. The word that matters is all. If you only have one condition, COUNTIF does the job, and my COUNTIF guide covers that. This page is about counting with two or more.

COUNTIFS syntax

Argument	Required?	What it holds
criteria_range1	Yes	The first range to test
criteria1	Yes	What to look for in that range, as a number, text, comparison or cell
criteria_range2, criteria2 and so on	No	Extra pairs, up to 127 in total

Unlike SUMIFS, there's no separate range to add up. COUNTIFS just counts the rows that pass.

The rule that makes COUNTIFS work: same-size ranges

Here's the catch. Each additional range must have the same number of rows and columns as criteria_range1. The ranges don't have to sit next to each other, but they do have to line up.

That's because of how COUNTIFS counts. Microsoft explains that each range's criteria is applied one cell at a time, and the count goes up by 1 only when all the cells at that position meet their criteria. So row 2 of the first range is checked alongside row 2 of the second range, row 3 alongside row 3, and so on. If the ranges are different sizes, there's nothing to pair up.

Microsoft's quota example

Microsoft's first example is a table of four salespeople, with Yes or No in columns B, C and D for whether each one beat their Q1, Q2 and Q3 quota. Two formulas show two different ways to use the same table:

Formula	What it counts	Result on Microsoft's page
=COUNTIFS(B2:D2,"=Yes")	How many quarters Davidoski beat quota, reading across one row	1
=COUNTIFS(B2:B5,"=Yes",C2:C5,"=Yes")	How many salespeople beat both the Q1 and the Q2 quota	2 (Burke and Sundaram)

The first formula only has one pair, so it's really doing COUNTIF's job across a row. The second is where COUNTIFS earns its keep: column B and column C are both checked for each salesperson, and only the rows where both say Yes are counted.

Counting between two numbers or before a date

A common use is counting values inside a range. You do it by testing the same range twice:

• =COUNTIFS(A2:A7,"<6",A2:A7,">1") counts the numbers between 1 and 6, not including 1 and 6. Microsoft's result is 4.
• =COUNTIFS(A2:A7, "<5",B2:B7,"<5/3/2011") counts the rows where the number in column A is below 5 and the date in column B is earlier than the date in the formula.

The second example mixes a number test and a date test in one formula. That's the kind of question a single COUNTIF can't answer: how many orders under a certain size came in before a certain day.

Criteria tips: quotes, empty cells and wildcards

Comparisons and text go in straight double quotes, as in "<6" or "=Yes". If the criteria argument is a reference to an empty cell, COUNTIFS treats that empty cell as a 0 value, which can give a count you didn't expect if a criteria cell hasn't been filled in yet.

Wildcards work in COUNTIFS criteria. A question mark matches any single character, an asterisk matches any sequence of characters, and a tilde in front of a real ? or * tells Excel to search for that character itself. Microsoft's COUNTIF page shows the idea with =COUNTIF(A2:A5,"?????es"), which counts seven-character words ending in es; the same patterns work inside each COUNTIFS pair.

COUNT, COUNTA, COUNTIF or COUNTIFS?

Excel has four counting functions that people mix up. Microsoft's own pointers on the COUNTA page make the choice simple:

Function	Counts	Conditions
COUNT	Cells with numbers	None
COUNTA	Cells that aren't empty	None
COUNTIF	Cells meeting one criterion	One
COUNTIFS	Rows meeting all criteria	Up to 127 pairs

COUNTA has a quirk worth knowing: it counts error values and the empty text that a formula returns as "", so a cell that looks blank can still be counted. If you need to count cells that hold words rather than numbers, I've written a separate guide to counting cells with text.

Why a count looks wrong

Microsoft's COUNTIF troubleshooting list is the best checklist I've found, and the same causes trip up multi-condition counts:

• Strings longer than 255 characters return wrong results. Join long strings with CONCATENATE or the ampersand.
• A #VALUE! error appears when the formula refers to a closed workbook.
• Criteria aren't case sensitive, so apples and APPLES match the same cells.
• Stray spaces at the start or end of a cell, or a mix of curly and straight quotes, give unexpected values. Try CLEAN or TRIM on the data.

Named ranges work too, which makes long COUNTIFS formulas much easier to read.

How to write a COUNTIFS formula

1. Click the result cell and type =COUNTIFS(
2. Select the first range to test, type a comma, then criteria 1. Text and comparisons go in quotes.
3. Repeat range, comma, criteria for each extra condition, keeping every range the same size.
4. Close the bracket and press Enter.

My verdict

COUNTIF for one condition, COUNTIFS the moment you've got two, and COUNTA when all you want to know is how many cells have anything in them. If a COUNTIFS result looks too low, check the ranges first: one range that's a row shorter than the others is the mistake I'd look for before anything else.

There's a separate write-up on this in this walkthrough of how to find and replace in Excel.

Frequently asked questions

What's the difference between COUNTIF and COUNTIFS?

COUNTIF counts cells that meet one criterion. COUNTIFS counts the rows where all of up to 127 criteria are met.

Do COUNTIFS ranges have to be the same size?

Yes. Each extra range must have the same number of rows and columns as the first, though they don't have to be next to each other.

How do I count values between two numbers?

Test the same range twice: once for values below the top number and once for values above the bottom one. Microsoft's example counts numbers between 1 and 6 this way.

Is COUNTIFS case sensitive?

No. Microsoft says criteria aren't case sensitive, so apples and APPLES match the same cells.

Can I use wildcards in COUNTIFS?

Yes. A question mark matches one character, an asterisk any sequence, and a tilde lets you find a real ? or *.