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

One Reply 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”)

Leave a Reply

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