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)
countifs multiple criteria

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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.

COUNTIFS count between date range

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")
COUNTIFS with wildcards

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.

Excel Download Practice file

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.