COUNTIFS in Excel: how to use it
COUNTIFS counts the rows that meet several conditions at once. For each condition you give a range and a value.
Try the first exercises for freeIn the lesson Counting with COUNTIFS
What does COUNTIFS do?
COUNTIFS counts the rows that meet all the conditions at once. For each condition, you give a pair: first the range Excel looks in, then what has to be in it.
A row only counts if every pair is true. So it's an AND count: from Leeds and a laptop. If you want to count what's from Leeds or from Bristol, add two COUNTIF formulas together.
When do you use COUNTIFS?
- How many laptops were sold in Leeds: =COUNTIFS(A2:A100,"Leeds",B2:B100,"Laptop").
- How many orders are between 100 and 500: =COUNTIFS(C2:C100,">=100",C2:C100,"<=500").
- How many students in group B passed: =COUNTIFS(A2:A30,"Group B",D2:D30,">=10").
- How many invoices from one customer are still open: =COUNTIFS(A2:A200,"Walker",E2:E200,"Open").
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
Filled in: =COUNTIFS(A2:A6,"Leeds",B2:B6,"Monitor")
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
COUNTIFS | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | COUNTIFS |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
range1 | The first column in which you check a condition. | A2:A6 |
criteria1 | What has to be in that column, such as "Leeds" or ">500". | "Leeds" |
range2 | The column for the second condition. It has to be the same length as range1. | B2:B6 |
criteria2 | What has to be in that second column. | "Monitor" |
, | A comma separates the arguments from each other. | , |
" " | You type text in quotation marks, such as "Leeds". A number or a cell, such as 500 or A2, you type without them. | |
... | You can give more, with a comma between each one. |
COUNTIFS step by step
E1 contains =COUNTIFS(A2:A6,"Leeds",B2:B6,"Monitor"). One row is both Leeds and Monitor. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- In E1, type the function with a bracket.
=COUNTIFS( - The first pair: select the regions in A2:A6, type a comma and then "Leeds".
=COUNTIFS(A2:A6,"Leeds" - Type a comma for the second pair: the products in B2:B6, another comma and "Monitor".
=COUNTIFS(A2:A6,"Leeds",B2:B6,"Monitor" - Close the bracket and press Enter: one row is both Leeds and Monitor.
=COUNTIFS(A2:A6,"Leeds",B2:B6,"Monitor")
More examples
Between two limits
E1 contains =COUNTIFS(C2:C6,">=300",C2:C6,"<=1000"). The same column twice: at least 300 and at most 1000. Three revenue figures are in between.
Text and number together
E1 contains =COUNTIFS(B2:B6,"Laptop",C2:C6,">1000"). Laptops with revenue over 1000: only the one from Leeds.
OR instead of AND
E1 contains =COUNTIF(A2:A6,"Leeds")+COUNTIF(A2:A6,"Bristol"). From Leeds or from Bristol: add up two COUNTIFs. COUNTIFS would look for rows that are both at once, and there aren't any.
AND, not OR
A row only counts if every condition is met. For 'or', add two COUNTIF formulas together.
Where it often goes wrong
- All ranges must be the same size, otherwise you get #VALUE!.
Practise this in the lesson Counting with COUNTIFS. The first exercises are free.
Frequently asked questions
How many conditions can I use?
Up to 127 pairs, but in practice you rarely need more than three.