Download the practice workbook 👉 HERE and follow along:
The COUNTIFS function is a powerful tool for Excel users. It helps you count cells based on multiple criteria, making data analysis easier and more accurate. By understanding how to use it, you can streamline your work and get more done in less time.
How to Use COUNTIFS in Excel
Here’s the COUNTIFS syntax:
=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)- criteria_range1: The first range to check.
- criteria1: The condition for the first range.
- criteria_range2, criteria2: More ranges and their conditions (optional).
COUNTIF VS COUNTIFS
The COUNTIF function is used to count the number of cells in a range that meet a single criterion. It’s straightforward and easy to use, making it perfect for simple counting tasks. Discover how to use the COUNTIF function in Excel with our detailed guide.
The COUNTIFS function, on the other hand, allows you to count cells based on multiple criteria across different ranges. This function is ideal for more complex scenarios where you need to apply multiple conditions.
COUNTIFS Excel with One Condition
In the example below, we want to count the products that were sold in February (Feb).
Formula
=COUNTIFS(A3:A21, F3)Explanation
- A3:A21: This is the range containing the months.
- F3: This is the condition we’re counting for; the month must be “Feb”.
The formula counts how many cells in the range A3 to A21 contain the month “Feb”.
Excel COUNTIFS with Multiple Conditions
If you have another condition, you can easily expand the COUNTIFS formula. For example, if you also want to filter by a specific product:
=COUNTIFS(A3:A21, F4, B3:B21, G4)Explanation:
- A3:A21: This is the range containing the months.
- F3: This is the first condition we’re counting for; the month must be “Feb”
- B3:B21: This is the range containing the products.
- G4: This is the second condition we’re filtering for; the product must be “Shirt white”
Adding Another Condition
To add another condition, simply expand the COUNTIFS formula. For example, if you also want to filter by country:
=COUNTIFS(A3:A21, F5, B3:B21, G5, C3:C21, H5)
Featured Course
Master Excel’s Essential Modern Functions
Count Between Dates
Sometimes you need to count entries within a specific date range. This is common when analyzing sales, project timelines, or any time-sensitive data.
Let’s say we want to count orders placed in February.
=COUNTIFS(A3:A21, ">"&G3, A3:A21, "<"&H3)Explanation:
- A3:A21: Range of order dates.
- “>”&G3”: Condition that the date should be on or after February 1, 2024.
- “<“&H3: Condition that the date should be on or before February 28, 2024.
The formula counts how many orders were placed in February 2024.
💡 When you use a logical operator with a date in Excel, you need to combine them correctly. In our example, the “>” symbol should be in quotes (“>”), but the ampersand (&) and the cell reference (G3) should not.
CONTIFS Using Wildcards
Wildcards are useful when you need to count entries that partially match a condition. This is helpful for analyzing product categories, customer names, or any data with similar prefixes or patterns.
Let’s say, we want to count orders for products starting with “Shirt” sold in February:
=COUNTIFS(C3:C21, "Shirt*", A3:A21, "Feb")
In this case, the * wildcard matches any number of characters following “Shirt”. This means “Shirt blue”, “Shirt white”, and “Shirt yellow” would all be included in the count.
Common Errors
- VALUE! Error: Occurs if your criteria ranges don’t match in size.
- DIV/0! Error: Happens when ranges have no data or criteria are set incorrectly.
Tips for Using COUNTIFS
- Use Quotes for Text and Operators: Put text and logical operators (>, <, =) in quotes. Numbers don’t need quotes.
- Use Wildcards for Partial Matches: Use an asterisk (*) for any sequence of characters or a question mark (?) for any single character.
- Match Range Sizes: Ensure your criteria ranges are the same size to avoid errors.
- Cell References: Use cell references for criteria to make formulas adaptable.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel COUNTIFS Function with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
