Excel COUNTIFS examples: counting with multiple criteria, including between two values

What does COUNTIFS do in Excel?

COUNTIFS counts the rows that meet all your conditions at once. Where COUNTIF asks one question, COUNTIFS asks as many as you need: is it clothing, and did it cost more than £5? =COUNTIFS(B2:B16,"Clothing",C2:C16,">5") returns 3, because three items of clothing cost more than £5. In a Dutch copy of Excel, the same function is called AANTALLEN.ALS.

Every example uses a week of sales at the Second Chance charity shop in Gloucester, the fourth client in our Excel course. If you'd rather learn the function step by step, follow the lesson on multiple conditions. If you're still counting with one condition, the COUNTIF examples show every kind of criteria. Here we look at what changes once a second condition comes in.

How do you write a COUNTIFS formula?

In pairs: a range, then the criteria that belong to that range. The first pair is required, and you can use up to 127 pairs (Microsoft). There's no separate range to count, like the sum range in SUMIFS: the function counts rows.

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

With Dutch or Belgian regional settings you'd write =AANTALLEN.ALS(B2:B16;"Clothing";C2:C16;">5"). Only the function name and the separator change.

How do you count with two conditions?

Coordinator Yasmina at Second Chance wants to know how much pricier clothing left the shop. This is the whole sheet, with all fifteen sales. G2 holds a price limit of £10, which we'll refer to in a moment.

=COUNTIFS(B2:B16,"Clothing",C2:C16,">5")

The formula returns 3. Excel goes down the rows and tests both conditions on each one. Clothing appears six times on the sheet, but only Dress green, Dressing gown and Dress black cost more than £5. The £15 radio drops out on its category, the T-shirts on their price. It doesn't matter that the conditions sit in different columns, because the function always looks at one row at a time.

Your turn. The exercise runs on Second Chance's real sheet, and you type the formula as you would in Excel.

Your formula ran in your own browser, on a real calculation engine. Then we run it again on a hidden version of the sheet, with other prices and volunteers. Anyone who just types 3 gets caught there.

How do you count between two values with COUNTIFS?

Use the same column twice, with a lower limit and an upper limit. This is the example most people look this function up for, because COUNTIF can't do it in one go. Excel tests both conditions on the same cell, and only what falls between the limits counts. Try it here.

A date range works the same way, because a date is a number to Excel. With a real date in column D, =COUNTIFS(D2:D16,">="&DATE(2026,11,1),D2:D16,"<"&DATE(2026,12,1)) would count the sales of all of November. For how that handles times on the last day, see SUMIFS with a date range.

Which criteria can you combine?

All the ones you know from the lesson on criteria: text, numbers, comparisons, wildcards and references to a cell, in any mix. Microsoft lists the same wildcards as for COUNTIF (Microsoft). On Second Chance's sheet, that looks like this:

The criteria with a cell are especially worth it. Put the limit in G2 and refer to it with ">"&G2, and Yasmina can change the limit without opening the formula. Only the operator goes in the quotation marks; the reference stays outside.

How do you build a summary by category and person?

With one formula you fill across and down, thanks to mixed references. Put the categories below each other in column K and the names side by side in row 1, then write this in L2:

=COUNTIFS($B$2:$B$16,$K2,$E$2:$E$16,L$1)

Fill that formula right and down, and the ranges stay put, while $K2 follows the row and L$1 follows the column. That gives you a cross table:

Together that makes 15, as many as there are rows on the sheet. It's a handy check too: if the total is off, a category or name somewhere has a typo.

Can COUNTIFS work with OR?

Not with ordinary criteria: all the conditions must hold at once. That's why =COUNTIFS(B2:B16,"Clothing",B2:B16,"Books") returns 0. No single cell is clothing and books at the same time. For one thing or another, add two formulas: =COUNTIFS(B2:B16,"Clothing")+COUNTIFS(B2:B16,"Books") returns 8, six items of clothing and two books.

That addition works because a row has only one category. Ask for clothing or anything of Sophie's, and one row can meet both. Six items of clothing plus five sales by Sophie makes 11, while the real answer is 8: Sophie's three items of clothing are in the sum twice. So subtract them again, with a third formula that tests both conditions together.

Why isn't my COUNTIFS working?

Most mistakes give no message, just a count that's too low. First check whether your conditions can hold together, then work through this list.

You get 0 even though the rows exist

Spaces are the usual suspect. You can't see a trailing space in a cell, but "Clothing" won't find that cell any more. Microsoft names leading and trailing spaces as a cause of unexpected results (Microsoft). ">G2" gives 0 too, because Excel then looks for the letters G2 rather than the value in that cell.

The formula returns #VALUE!

Then the ranges usually aren't the same size. Each additional range must have as many rows and columns as the first (Microsoft). They don't have to sit next to each other, but they do have to be the same length. =COUNTIFS(B2:B16,"Clothing",E2:E15,"Sophie") stops on that single row of difference.

Criteria that refer directly to an empty cell are read as 0. With H1 empty, =COUNTIFS(C2:C16,H1) looks for prices of £0, and returns 0 without a warning.

A range is missing

Each criteria belongs to a range of its own. =COUNTIFS(E2:E16,"Omar",">5") doesn't work, because ">5" doesn't say which column to look in. Even when two conditions are about the same column, you write that column twice.

Which functions are like COUNTIFS?

COUNTIFS is the counter of a family. The other members do the same thing but work out something else, which is why they start with the range of numbers.

If you want amounts rather than items, read SUMIFS with examples. Averages with a condition are in AVERAGEIF and AVERAGEIFS, and every function with its Dutch name is on the Excel cheat sheet.

If you want to master counting with conditions, follow module 4 of the Excel course. You start with COUNTIF and build up to multiple conditions in six short lessons, and the first modules are free.