Count Cells with Two or Multiple Conditions in Excel (Step-by-Step Guide)

Want to count cells in Excel only when two conditions or multiple conditions are met? This is a common task in sales reports, employee lists, survey data, and inventory tables. Excel can do this with the COUNTIFS function, which counts only the cells that meet all criteria. If you want a faster, easier option, Kutools for Excel also provides a visual way to count cells that match two conditions (AND logic) without writing the formula yourself.

Count cells with conditions using formulas (COUNTIFS)

• Count cells with two conditions
• Count cells with multiple conditions

Faster way to count cells with two conditions in Excel (no formula)

COUNTIFS vs. Kutools for Excel: which method is better?

FAQ

Count cells with conditions using formulas (COUNTIFS)

In Excel, the most common way to count cells based on conditions is to use COUNTIFS. This function is designed for counting cells that meet multiple criteria, and it uses AND logic, which means every condition must be true at the same time.

The basic syntax is:

=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2, ...)

Each additional pair adds one more condition. This makes COUNTIFS flexible and powerful for structured worksheets.

Count cells with two conditions

Suppose you have a sales table where column A contains product names and column B contains regions. If you want to count how many rows have Product = Apple and Region = East, use this formula:

=COUNTIFS(A2:A13,"Apple",B2:B13,"East")

Result: This formula counts only the rows where both conditions are true at the same time.

If you prefer to keep the criteria in cells, for example with Apple in F1 and East in F2, use this formula:

=COUNTIFS(A2:A13,F1,B2:B13,F2)

This is one of the most common COUNTIFS uses in Excel, especially when you need to count records based on two fields, such as product and region, department and status, or name and score.

Count cells with multiple conditions

You can also use COUNTIFS to count rows based on three or more conditions. For example, if column A contains products, column B contains regions, and column C contains quantities, the following formula counts rows where:

• Product = Apple
• Region = East
• Quantity > 200

=COUNTIFS(A2:A13,"Apple",B2:B13,"East",C2:C13,">200")

Result: This formula counts only the rows that satisfy all three conditions.

If you prefer to keep the criteria in cells, for example with Apple in F1, East in F2, and >200 in F3, use this formula:

=COUNTIFS(A2:A13,F1,B2:B13,F2,C2:C13,F3)

Using cell references makes the formula easier to update, especially when you need to test different criteria without rewriting the formula each time.

COUNTIFS is good when you want a built-in, formula-based solution. However, as the number of conditions increases, formulas become harder to read, edit, and troubleshoot, especially for beginners or when criteria need to change often.

Faster way to count cells with two conditions in Excel (no formula)

If you only need to count cells based on two conditions and do not want to remember COUNTIFS syntax, Kutools for Excel offers a much easier option. Its Count cells with two conditions (and) feature lets you set the ranges and criteria visually and return the result in just a few clicks.

1. Select a blank cell where you want the result to appear.
2. Click Kutools > Formula Helper > Statistical > Count cells with two conditions (and).
   
3. In the Formulas Helper dialog box, do the following:
   • In Range 1, select the first range you want to evaluate.
   • In Criteria 1, specify the first condition.
   • In Range 2, select the second range you want to evaluate.
   • In Criteria 2, specify the second condition.
   • Click OK to return the result.
     

Result: You get the count instantly based on the two selected conditions, without writing a formula yourself.

COUNTIFS vs. Kutools for Excel: which method is better?

Both methods work well, but they are designed for different users and scenarios.

In short, use COUNTIFS when you need maximum flexibility, especially for multiple criteria. Use Kutools for Excel when you want a faster and easier way to count cells with two conditions.

FAQ

What is the difference between COUNTIF and COUNTIFS in Excel?

COUNTIF counts cells based on a single condition, while COUNTIFS counts cells based on two or more conditions. If you need to count rows only when multiple criteria are met, COUNTIFS is the correct function to use.

Does COUNTIFS use AND or OR logic?

COUNTIFS uses AND logic. That means every condition in the formula must be true for a row to be counted.

Can I count cells with more than two conditions in Excel?

Yes. COUNTIFS supports multiple criteria pairs, so you can count cells with three, four, or more conditions as needed.

Is there a way to count cells with two conditions without using a formula?

Yes. Kutools for Excel provides the Count cells with two conditions (and) feature, which lets you count matching cells through a dialog box instead of entering a COUNTIFS formula manually.