Excel: Use COUNTIFS to Count Across Multiple Columns


You can use the COUNTIFS function in Excel to count the number of rows that meet criteria across multiple columns.

Here are three common ways to use the COUNTIFS function across multiple columns:

Method 1: Use COUNTIFS Across Multiple Columns with AND Logic

=COUNTIFS(A2:A11, "Mavs", B2:B11, "Guard", C2:C11, ">15")

This particular formula counts the number of rows where the value in A2:A11 is equal to “Mavs” and the value in B2:B11 is equal to “Guard” and the value in C2:C11 is greater than 15.

Method 2: Use COUNTIFS Across Multiple Columns with OR Logic

=COUNTIFS(A2:A11, "Mavs")+COUNTIFS(A2:A11, "<>Mavs", B2:B11, "Guard")

This particular formula counts the number of rows where the value in A2:A11 is equal to “Mavs” or the value in B2:B11 is equal to “Guard”.

Method 3: Use COUNTIFS Across Multiple Columns with Both AND / OR Logic

=COUNTIFS(A2:A11, "Mavs", B2:B11, "Guard") + COUNTIFS(A2:A11, "Spurs", B2:B11, "Center")

This particular formula counts the number of rows where the value in A2:A11 is “Mavs” and the value in B2:B11 is “Guard” or where the value in A2:A11 is “Spurs” and the value in B2:B11 is “Center”.

The following examples show how to use each method in practice with the following dataset in Excel that contains information about various basketball players:

Example 1: Use COUNTIFS Across Multiple Columns with AND Logic

We can type the following formula into cell D2 to count the number of rows where the team is “Mavs” and the position is “Guard” and the points value is greater than 15:

=COUNTIFS(A2:A11, "Mavs", B2:B11, "Guard", C2:C11, ">15")

The following screenshot shows how to use this formula in practice:

The total number of players that meet these three criteria is 1.

Specifically, we can see that the player in row number 3 is the only player to meet all three of these criteria.

Example 2: Use COUNTIFS Across Multiple Columns with OR Logic

We can type the following formula into cell D2 to count the number of rows where the team is “Mavs” or the position is “Guard:

=COUNTIFS(A2:A11, "Mavs")+COUNTIFS(A2:A11, "<>Mavs", B2:B11, "Guard")

The following screenshot shows how to use this formula in practice:

The total number of players that meet these three criteria is 7.

Note: In the second COUNTIFS function we had to specify that the team should not be equal to “Mavs” so that we didn’t double count the guards on the Mavs team.

Example 3: Use COUNTIFS Across Multiple Columns with Both AND / OR Logic

We can type the following formula into cell D2 to count the number of rows where the player is a Mavs Guard or a Spurs Center:

=COUNTIFS(A2:A11, "Mavs", B2:B11, "Guard") + COUNTIFS(A2:A11, "Spurs", B2:B11, "Center")

The following screenshot shows how to use this formula in practice:

The total number of players that meet these three criteria is 3.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

How to Use COUNTIFS with a Date Range in Excel
How to Use COUNTIF with OR in Excel
How to COUNTIF Greater Than But Less Than Some Number in Excel

3 Replies to “Excel: Use COUNTIFS to Count Across Multiple Columns”

  1. Hi Sir
    I was trying to calculate/count the number of Very low, Law, Medium, Good, Very Good (Evaluation results given by students). The questions (criteria, 35 in #) were listed from column BN to CV. The instructors were teaching several sections (data collected for each section in column (AL to BM). The raw data is on the other sheet named as “FreshmanEvaluation”. My plan was to count the name of the instructor (eg. Dr. Abel) from column AL to BM and then count the number of “Low” for the column referring each questions.
    so what is wrong with the command below?
    =COUNTIFS(FreshmanEvaluation!AL:BM,”Dr. Abel”,FreshmanEvaluation!BN:BN,”Low”)

  2. What if I want to check the data across multiple columns? For example: instead of =COUNTIFS(A2:A11, “Mavs”, B2:B11, “Guard”, C2:C11, “>15”) I have two columns to check data from i.e. =COUNTIFS(A2:A11, “Mavs”, B2:B11, “Guard”, C2:D11, “>15”). The formula doesn’t support that.

    1. Hi Parag…You’re right that `COUNTIFS` does not support specifying multiple columns (like `C2:D11`) in a single criterion for comparison. However, you can solve this issue by using a workaround with an **array formula** or by summing individual `COUNTIFS` for each column.

      Here are two approaches to achieve your goal of checking multiple columns:

      ### 1. **Using SUMPRODUCT for Multiple Columns**

      You can use `SUMPRODUCT` to count rows where values in either `Column C` **or** `Column D` meet your condition. Here’s how you can modify your formula:

      “`excel
      =SUMPRODUCT((A2:A11=”Mavs”)*(B2:B11=”Guard”)*((C2:C11>15)+(D2:D11>15)>0))
      “`

      ### Explanation:
      – `(A2:A11=”Mavs”)` checks if each row in column A contains “Mavs”.
      – `(B2:B11=”Guard”)` checks if each row in column B contains “Guard”.
      – `((C2:C11>15)+(D2:D11>15)>0)` ensures that either column C **or** column D has values greater than 15. The `>0` ensures that if either column meets the condition, the row will be counted.

      ### 2. **Using Helper Columns (Simpler)**

      If you are okay with adding an additional helper column to your dataset, you can create a new column (`E`, for example) that checks if either value in `C` or `D` meets your condition and then apply a `COUNTIFS` to that helper column:

      1. In `E2`, add the following formula:
      “`excel
      =IF(OR(C2>15, D2>15), 1, 0)
      “`

      2. Drag this formula down to cover all rows in the range.

      3. Then use a `COUNTIFS` that references the new helper column `E`:

      “`excel
      =COUNTIFS(A2:A11, “Mavs”, B2:B11, “Guard”, E2:E11, 1)
      “`

      This solution splits the problem into two steps: first identifying which rows satisfy the condition in either `C` or `D`, and then counting those rows with `COUNTIFS`.

      Let me know if you’d like further clarification!

Leave a Reply

Your email address will not be published. Required fields are marked *