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
