COUNTIFS in Excel
Last updated
COUNTIFS counts the rows that meet several conditions at once. For example, =COUNTIFS(C2:C13,"High",E2:E13,"Open") counts the tickets whose priority is High and whose status is Open. Each condition is a pair: a range to check, then the criterion it must meet.
COUNTIFS syntax
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], …)
- criteria_range1
- The first range to check, e.g.
C2:C13. Lock it with$($C$2:$C$13) before copying the formula. - criteria1
- The condition for that range: a text in quotes (
"High"), a comparison in quotes (">=2"), a wildcard ("Billing*") or a cell reference (H2). It is not case-sensitive. - criteria_range2, criteria2, …
- Optional extra pairs. Every range must have the same number of rows and columns as the first one. A row is counted only if it meets all the conditions.
Practice COUNTIFS here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Count tickets that meet two conditions
In H2, use COUNTIFS to count the tickets that have the priority High in C2:C13 and the status Open in E2:E13.
| Ticket no. | Channel | Priority | Topic | Status | Time spent (h) | Open high-priority tickets | |
| T-3101 | High | Billing – Refund | Open | 6 | |||
| T-3102 | Phone | High | Account – Login | Closed | 1 | ||
| T-3103 | Chat | Low | Bug report | Closed | 2 | ||
| T-3104 | Medium | Billing – Invoice | Closed | 8 | |||
| T-3105 | Phone | Medium | Account – Password | Open | 3 | ||
| T-3106 | Chat | High | Billing – Refund | Open | 12 | ||
| T-3107 | Low | Account – Password | Closed | 1 | |||
| T-3108 | Phone | Low | Billing – Invoice | Closed | 5 | ||
| T-3109 | Chat | Medium | Account – Login | Open | 4 | ||
| T-3110 | High | Bug report | Open | 9 | |||
| T-3111 | Phone | High | Billing – Invoice | Closed | 3 | ||
| T-3112 | Chat | Medium | Billing – Refund | Closed | 7 |
How to use COUNTIFS, step by step
- Click the cell where you want the count and type
=COUNTIFS(. - Select the first range to check, type a comma, then type its condition in double quotes or click the cell that holds it.
- Type a comma and add the next pair the same way: range, comma, condition. Use ranges of the same size.
- Close the parenthesis and press Enter. Press F4 on each range first to lock it with $ if you will copy the formula.
COUNTIFS examples
Two text conditions
=COUNTIFS(C2:C13,"High",E2:E13,"Open")
Counts the rows where column C says High and column E says Open, both on the same row.
Count values between two limits
=COUNTIFS(F2:F13,">=2",F2:F13,"<8")
Uses the same range twice: at least 2 and less than 8. A value of exactly 2 is counted, exactly 8 is not.
Take the conditions from cells
=COUNTIFS($B$2:$B$13,H2,$E$2:$E$13,I2)
Counts the rows that match H2 in column B and I2 in column E. To combine a comparison with a cell, join them with &: ">"&H2.
Wildcard and comparison
=COUNTIFS(D2:D13,"Billing*",F2:F13,">4")
"Billing*" matches any text that starts with Billing, and ">4" keeps only the values above 4.
Common COUNTIFS problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| 0 instead of a count | No row meets all the conditions at once. This often happens when you put two values for the same column, e.g. "High" and "Medium", expecting an OR. | COUNTIFS combines conditions with AND. For an OR, add two COUNTIFS together. Also check spelling and extra spaces. |
| #VALUE! | The ranges don’t have the same size, e.g. C2:C13 with E2:E12. | Give every range the same rows and columns. |
| Wrong counts after copying | The ranges weren’t locked, so they slid when you copied the formula down or across. | Lock the ranges with $ ($C$2:$C$13), and use mixed references like $H2 or I$1 for criteria in a grid. |
| #NAME? | A text criterion without quotes, e.g. =COUNTIFS(C2:C13,High). | Put text criteria in double quotes: "High". |
COUNTIFS vs COUNTIF
COUNTIF takes one range and one condition. COUNTIFS takes one or more range and condition pairs and counts only the rows that meet all of them, e.g. tickets that are High priority and still Open. With a single pair, COUNTIFS gives the same result as COUNTIF, so you can use COUNTIFS for every conditional count.
Frequently asked questions
How do I count with an OR condition?
COUNTIFS always combines its conditions with AND. For an OR, add two counts: =COUNTIFS(C2:C13,"High",E2:E13,"Open")+COUNTIFS(C2:C13,"Medium",E2:E13,"Open").
How do I count dates between two dates?
Use the date column twice and join each operator to a cell with &: =COUNTIFS(A2:A100,">="&H1,A2:A100,"<"&H2) counts the dates from H1 up to, but not including, H2.
How many conditions can COUNTIFS take?
Up to 127 range and condition pairs. Every range must have the same size.
Can I use COUNTIFS with only one condition?
Yes. =COUNTIFS(C2:C13,"High") works exactly like =COUNTIF(C2:C13,"High").
Keep practising
All 5 COUNTIFS exercises, with explanations and progress tracking.
Open the COUNTIFS practice set