Count cells in a column if (not) meeting one of multiple criteria in another column in Excel

We can apply the some formulas to count cells if they contain one of multiple criteria in Excel.

(1) Formula 1: =COUNTIF(Range, criteria 1)+COUNTIF(Range, Criteria 2)+ … + COUNTIF(Range, Criteria N)

For example we will count the cells containing "Apple" or "Lychee" in Range A1:E15, you can apply the formula =COUNTIF(A1:E15,"Apple")+COUNTIF(A1:E15,"Lychee").

(2) Formula 2:=SUM(COUNTIF(Range,{"Criteria 1","Criteria 2","Criteria 3",…,"Criteria N"}))

In our case, to count cells containing "Apple" or "Lychee" in Range A1:E15 we need to change the formulas as =SUM(COUNTIF(A1:E15,{"Apple","Lychee"})).

(3) Formula 3:=SUMPRODUCT((Range="Criteria 1")+(Range="Criteria 2")+ … + (Range="Criteria N"))

In our case, the formula should be changed as =SUMPRODUCT((A1:E15="Apple")+(A1:E15="Lychee")) to count cells containing "Apple" or "Lychee" in Range A1:E15.

Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future! Read more…     Free trial

This method will introduce Kutools for Excel's Select Special Cells utility to count cells if meeting one of both conditions from the selected range. Please do as follows:

1. Select the range that you will count cells if one of both conditions met, and click Kutools > Select > Select Specific Cells. See screenshot:

2. In the opening Select Specific Cells dialog box as above screenshot shown, please:

(1) Check the Cell option in the Selection type section;

(2) Go to the Specific type section, click the first drop down list and select Equals (or Contains), and then type the first condition into the following box;

(3) Go ahead to click the second drop down list and select Equals (or Contains), and type type the second condition into the following box; and then please check the Or option. See screenshot above.

3. Click the Ok button.

And then you will see all cells meeting one of both conditions are selected, and a dialog box pops out and show you how many cells have been selected. Click OK button to close it.

This method will introduce Kutools for Excel's Compare Ranges utility to quickly count cells in a column if cells meet one of multiple criteria in Excel.

1. Click Kutools > Select > Select Same & Different Cells.

2. In the opening Compare Ranges dialog box, please do as follows:

(1) Specify the column you will count cells from in the Find values in box;

(2) Specify the column containing criteria you will count based on in the According to box;

(3) Check the Same Values option in the Find section.

3. Click the Ok button. And then you will see another Compare Ranges dialog box comes out and shows how many cells have been selected. This number is the total number of cells met one of multiple criteria. See screenshot:

Click the Ok button to close this Compare Ranges dialog box. 

How to count if cell does not contain text in Excel?

How to count if cells contain any date/data in Excel?

How to Countif by date/month/year and date range in Excel?

How to Countif filtered data/list in Excel?