You can use the COUNTIFS function to count the values in ranges that meet certain criteria. The criteria can contain a not equal logic as well. However, the COUNTIFS function requires individual arguments for each range-criteria pair. This requirement can make it tedious to enter the criteria for each pair, and make it hard to maintain. In this guide, we’re going to show an alternative way to do a cell count if not equal to a variable, or even an error message using various formulas.
The alternative formula relies on the MATCH function and its value search capabilities. The MATCH function returns #N/A when the lookup value doesn't exist in the lookup array. To do a cell count if not equal to a variable, the idea is to count the #N/A values returned by the MATCH function.
The following function shows the generic syntax of the MATCH function:
Unlike its traditional use cases, we are using another range as the lookup value. This approach makes the MATCH function return an array. This array contains the index numbers of found values, and the #N/A values for when items are not found in the given range. For example;
Because the #N/A values represent all values except for the ones you want to exclude. For doing a cell count if not equal to, the next step is to find count number of #N/A values.
We can use the ISNA function to convert the #N/A values into TRUE logical values and double minus (double unary) (--) operator evaluate TRUE values as one (1).
The final step is to sum the ones (1) which also represent each individual value in the raw data, except for the you want to exclude. For this, you need to use the SUMPRODUCT function instead of the SUM function. The SUMPRODUCT function can evaluate arrays without using CSE key combination.
