How to Use the COUNTIFS Function in Excel to Count Cells That Meet Multiple Criteria

The COUNTIFS function is one of the most useful count functions in Excel It allows you to count cells in a range that meet multiple criteria that you specify

With COUNTIFS, you can count cells based on two or more conditions For example, you can count cells that contain a certain text value and fall between two dates Or cells that contain a number greater than a threshold and have a certain color fill. The possibilities are endless.

In this article, I’ll explain what the COUNTIFS function does, show you the syntax, and provide step-by-step examples of using COUNTIFS to count cells based on various criteria.

What COUNTIFS Does

COUNTIFS counts the number of cells in a range that meet multiple criteria. The criteria are provided in the form of conditions that specify what to count.

Here’s what it looks like:

=COUNTIFS(range1, criteria1, [range2, criteria2], …)

Where:

• range1, range2, etc. are the ranges of cells to count.
• criteria1, criteria2, etc. are the conditions that each range must meet to be counted.

COUNTIFS is designed for AND logic, meaning all criteria specified must be met for a cell to be included in the count.

The result of COUNTIFS is a number representing the count of qualifying cells.

COUNTIFS Syntax

Here is the syntax for the COUNTIFS function:

=COUNTIFS(range1, criteria1, [range2, criteria2],...)

The syntax has these arguments:

• range1 – The first range of cells to count. Required.
• criteria1 – The criteria that cells in range1 must meet. Required.
• range2, criteria2, … – Additional ranges and criteria. Optional. You can include up to 127 range/criteria pairs.

Ranges can be a cell range like A1:A10 or named ranges. Criteria can be a number, expression, cell reference or text. Criteria is applied to the range with the same index number (i.e. criteria1 applies to range1, criteria2 to range2, etc.)

The ranges do not need to be the same size and shape. COUNTIFS handles this by counting only cells that exist in all of the specified ranges.

Let’s look at some examples of using COUNTIFS with different criteria.

COUNTIFS Examples

Here are examples of COUNTIFS formulas with different types of criteria:

Count cells that equal a value:

=COUNTIFS(A1:A10,"Apple")

Count cells greater than a number:

=COUNTIFS(B1:B10,">9") 

Count cells that contain specific text:

=COUNTIFS(C1:C10,"*South*") 

Count cells between two dates:

=COUNTIFS(D1:D10,">=1/1/2017",D1:D10,"<=12/31/2017")

Count cells that match two conditions:

=COUNTIFS(E1:E10,"Cherry",F1:F10,"=A")

Now let’s look at the step-by-step process of using the COUNTIFS function.

How to Use COUNTIFS in Excel

Follow these steps to use COUNTIFS to count cells based on criteria you specify:

1. Select the cell where you want the result

   Click the cell where you want the COUNTIFS formula to output the count.

2. Type =COUNTIFS(

   Start the formula by typing =COUNTIFS(

3. Select the first range

   After the opening parenthesis, select the first range of cells to count. You can select a range manually or enter it as a named range or cell reference.

4. Type a comma ( , )

   Enter a comma to separate the first range from the first criteria.

5. Enter the first criteria

   After the comma, enter the criteria that cells in the first range must meet. Enclose text criteria in double quotes.

6. Enter additional range/criteria pairs (optional)

   To specify additional criteria, enter a comma, then the next range, comma, criteria, etc. The ranges and criteria should match positions.

7. Close the parenthesis

   Finish the formula by entering a closing ) parenthesis.

8. Press Enter to complete the formula

   After typing the ending parenthesis, press Enter to complete the formula.

9. Check the result

   The result is the count of cells that meet all of the criteria. Double click the cell to check the formula syntax.

Let’s see a COUNTIFS example formula:

=COUNTIFS(A1:A10,"Apple",B1:B10,">8")

This counts Apple values in A1:A10 that have a number greater than 8 in B1:B10.

By mixing and matching different types of criteria, you can create COUNTIFS formulas to count just about anything.

Here are some things to keep in mind when using COUNTIFS:

• Enclose text criteria in double quotes
• Use comparison operators like >, <, >=, <= for numeric criteria
• Reference specific cells for criteria to make formulas more dynamic
• The AND logic means all criteria must be met to count a cell

When to Use COUNTIFS

Here are examples of situations where COUNTIFS can help you analyze your data:

• Count orders over $500 for the East region
• Count new customers that signed up in Q1
• Count sales reps with over 10 deals won in March
• Count projects completed ahead of schedule and under budget

The key is COUNTIFS can count occurrences when multiple conditions must be met simultaneously. This provides flexibility not found in simpler functions like COUNTIF.

COUNTIFS vs COUNTIF

COUNTIFS is useful if you need to count cells based on two or more criteria. For counting cells based on only one criteria, you can use the COUNTIF function.

COUNTIF has a simpler syntax with just one range and one criteria:

=COUNTIF(range,criteria)

So when do you use COUNTIF vs COUNTIFS?

• Use COUNTIF when you want to count cells that meet a single criteria
• Use COUNTIFS when you need to count cells that meet two or more criteria

Knowing when to use COUNTIF vs COUNTIFS can help you write better Excel formulas.

Tips for Using COUNTIFS

Here are some tips to use COUNTIFS effectively:

• Put the criteria range with the most matches first to optimize performance
• Reference criteria cells instead of specifying literal values to make formulas more dynamic
• Use tab names to reference ranges on different sheets or named ranges for readability
• Add new criteria instead of full ranges to make COUNTIFS easy to modify
• Check for proper syntax and matching ranges and criteria
• Combine COUNTIFS with other functions like SUM, AVERAGE, MIN etc. to analyze subsets of data

Following these tips can help you get the most out of the powerful COUNTIFS function in Excel.

Examples of Using COUNTIFS for Analysis

Let’s look at some examples of using COUNTIFS to analyze real data:

Sales Analysis:

=COUNTIFS(Sales[Region],"West",Sales[Amount],">50000")

Counts number of sales over $50,000 in the West region.

Inventory Analysis:

=COUNTIFS(Inventory[Item],"*Desk*",Inventory[Number],"<6")

Counts desk models with less than 6 units in stock.

HR Analysis:

=COUNTIFS(Employees[Department],"Engineering",Employees[Hire Date],">1/1/2019") 

Counts employees hired after 1/1/2019 in the Engineering department.

Student Grades Analysis:

=COUNTIFS(Grades[Student ID],A1,Grades[Grade],">85")

Counts grades above 85 for the student in cell A1.

The ability to add multiple criteria makes COUNTIFS useful for extracting insights from your data.

Troubleshooting Errors

Here are some common errors and fixes when using COUNTIFS:

#VALUE! error – Occurs if a reference range is invalid. Check ranges for proper syntax and to ensure they refer to existing cells.

#NAME? error – Appears if a named range is not defined. Check for typos in named range names. Define named ranges if needed.

Wrong count – Double check criteria is matching up with the intended range. Ranges and criteria must align positionally (i.e. criteria1 with range1, criteria2 with range2, etc.)

Slow calculation – Put the range with the most potential matches first. Add additional range/criteria pairs instead of full ranges. Limit formula size where possible.

Taking the time to correctly structure your COUNTIFS formulas and checking for errors can help avoid problems.

Next Steps

The COUNTIFS function is a versatile tool for counting cells based on multiple