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).
What Is the Difference Between COUNTIF and 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.
How Do You Use COUNTIFS with a Single 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”.
How Do You Use 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”
How Do You Add a Third Condition to COUNTIFS?
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
FILTER, SORT, UNIQUE, XLOOKUP, SEQUENCE. The Excel functions most professionals were never taught, and the ones that turn hours of work into minutes.
How Do You Count Between Two Dates with COUNTIFS?
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.
How Do You Use Wildcards in COUNTIFS?
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.
What Are the Most Common COUNTIFS 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.
What Are the Best Practices 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.
To find and highlight the duplicate values that COUNTIFS identifies, see our guide on how to find duplicates in Excel.
