How to Use COUNTIF with Multiple Criteria in Same Column in Excel

To use COUNTIF with multiple criteria in the same column in Excel, you can combine multiple COUNTIF functions using addition or use the SUMPRODUCT function with multiple criteria. In this tutorial, we'll explore both methods.

Method 1: Combining Multiple COUNTIF Functions

1. Open your Excel workbook and select the cell where you want to display the result.
2. To count the number of cells that meet multiple criteria, use the following formula:

   =COUNTIF(range, criteria1) + COUNTIF(range, criteria2) - COUNTIFS(range, criteria1, range, criteria2)

   Replace range with the range of cells you want to count, and replace criteria1 and criteria2 with the conditions you want to count.
3. Press Enter to apply the formula.

Example

Let's say you have a list of grades in column A (A2:A10), and you want to count the number of grades that are equal to "A" or "B". Here's how you can use the COUNTIF function to count these cells:

1. Select the cell where you want to display the result (for example, B1).
2. Enter the following formula:

   =COUNTIF(A2:A10, "A") + COUNTIF(A2:A10, "B")

3. Press Enter to apply the formula.

The result in B1 will show the total number of cells in the range A2:A10 with a grade of "A" or "B".

Method 2: Using SUMPRODUCT Function

1. Open your Excel workbook and select the cell where you want to display the result.
2. To count the number of cells that meet multiple criteria, use the following formula:

   =SUMPRODUCT(--((range=criteria1)+(range=criteria2)))

   Replace range with the range of cells you want to count, and replace criteria1 and criteria2 with the conditions you want to count.
3. Press Enter to apply the formula.

Example

Using the same example as before, you have a list of grades in column A (A2:A10), and you want to count the number of grades that are equal to "A" or "B". This time, we'll use the SUMPRODUCT function:

1. Select the cell where you want to display the result (for example, B1).
2. Enter the following formula:

   =SUMPRODUCT(--((A2:A10="A")+(A2:A10="B")))

3. Press Enter to apply the formula.

The result in B1 will show the total number of cells in the range A2:A10 with a grade of "A" or "B".