How to Use Excel COUNTIF That Does Not Contain Multiple Criteria

We are used to applying the COUNTIF function in Excel to count the cells according to given criteria but it’s a little bit difficult to count the cells that don’t contain multiple criteria. In this article, we will learn about how to count cells that are not equal to many things using the COUNTIF function in Excel.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Introduction to Excel COUNTIF Function

The COUNTIF function is one of the statistical functions to count the number of cells that meet a criterion.

In its simplest form, COUNTIF says:

=COUNTIF(range, criteria)

Range= The data range where you will look at the value.

Criteria= The condition, according to that you will look up from the range.


Use of COUNTIF Function in Excel That Does Not Contain Multiple Criteria

Let’s get introduced to our dataset first. I have placed some fruit and cloth items and their prices in the dataset. Now I’ll use the COUNTIF function, SUM function, and COUNTA function to exclude the cloth items from the fruit items. The SUM function adds values. And the COUNTA function will calculate the number of cells that are not blank within a given set of values. I have merged Cells C15 and C16 to apply the formula.

Excel COUNTIF Function That Does Not Contain Multiple Criteria

Steps:

➥ Activate the merged cell.

➥ Write the formula given below-

=COUNTA(B5:B12)-SUM(COUNTIF(B5:B12,B15:B16))

➥ Just hit the Enter button then to get the result.

Excel COUNTIF Function That Does Not Contain Multiple Criteria

Now you will notice that the counted result excluding cloth items is showing.

👇 How Does the Formula Work?

COUNTIF(B5:B12,B15:B16)

It will count the cells from the range (B5:B12) according to the match criteria from the range (B15:B16). That will return as-

{1;1}

SUM(COUNTIF(B5:B12,B15:B16))

Now the SUM function will sum up the array and will result in as-

{2}

COUNTA(B5:B12)-SUM(COUNTIF(B5:B12,B15:B16))

The COUNTA function will count the total non-blank cells from the range (B5:B12). Then we have substracted the previous sum value from it. Then we’ll get our final result:

{6}

Note: You can use the SUMPRODUCT function alternative of SUM function as we are working with an array.

Read more: COUNTIF Multiple Ranges Same Criteria in Excel


Similar Readings:


Alternative to the Excel COUNTIF Function That Does Not Incorporate Several Criteria

Now I’ll show two alternative methods to count cells that are not equal to many things.

1. Use of Excel COUNTIFS Function That Does Not Enclose Multiple Criteria

Here, we’ll use the COUNTIFS function to count cells that do not contain multiple criteria. COUNTIFS function is used to count the number of cells that fulfill a single criterion or multiple criteria in the same or different ranges in Excel.

Steps:

➥ By activating the merged cell type the formula-

=COUNTIFS(B5:B12,"<>"&B15,B5:B12,"<>"&B16)

➥ Then press the Enter button.

Excel COUNTIFS Function That Does Not Contain Multiple Criteria

And we’ll spot that we have found our expected result now.


2. Combination of SUM, ISNA, and MATCH Functions That Does Not Contain Different Conditions in Excel

In this alternative method, we’ll use the combination of the SUM, ISNA, and MATCH Functions to do the same operation. The ISNA function checks whether a cell contains a “#N/A” error or not. The MATCH function searches for a value in an array and returns the relative position of that item.

Steps:

➥ In the merged cell write the formula given below-

=SUM(--(ISNA(MATCH(B5:B12,B15:B16,0))))

➥ Hit the Enter button to get the output.

SUM, ISNA, And MATCH Functions That Does Not Include Different Conditions in Excel

You will observe that we have gotten the same answer using this formula.

👇 Breakdown of the formula:

MATCH(B5:B12,B15:B16,0)

The MATCH function is used to find cells not equal to “T-Shirt” or “Jacket“. This function returns the position of matching number values. It returns #N/A for all the other values.So it will return as-

{#N/A;#N/A;1;#N/A;#N/A;2;#N/A;#N/A}

ISNA(MATCH(B5:B12,B15:B16,0))

The #N/A results are the ones we’re looking for since they represent values not equal to “T-Shirt” or “Jacket“. Then we have used the ISNA function to convert these values to TRUE, and the numbers to FALSE. After that, the result will be as like-

{TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE}

–(ISNA(MATCH(B5:B12,B15:B16,0)))

Then we have used a double negative to switch TRUE to 1 and FALSE to zero. The resulting array will look like this:

{1;1;0;1;1;0;1;1}

SUM(–(ISNA(MATCH(B5:B12,B15:B16,0))))

Finally, the SUM function will sum up the array and will give the counted result:

{6}

Note: Here we can use the SUMPRODUCT function too instead of the SUM function as we are working with an array.


Conclusion

I hope all of the methods described above will be useful enough to count cells that do not contain multiple criteria. Feel free to ask any questions in the comment section and please give me feedback.


Related Readings

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo