Explanation
In this example, the goal is to count the number of cells in a range that do not contain errors. The best way to solve this problem is to use the SUMPRODUCT function together with the ISERROR function. You can also use the COUNTIF function or COUNTIFS function to exclude specific errors. Both approaches are explained below.
COUNTIF function
One way to count cells that do not contain errors is to use the COUNTIF function like this:
=COUNTIF(B5:B14,"<>#N/A") // returns 9
For criteria, we use the not equal to operator (<>) with #N/A. Notice both values are enclosed in double quotes. COUNTIF returns 9 since there are nine cells in B5:B15 that do not contain the #N/A error. If we switch to the COUNTIFS function , we can exclude more than one kind of error like this:
=COUNTIFS(B5:B14,"<>#N/A",B5:B14,"<>#VALUE!") // returns 8
COUNTIFS returns 9 since there are eight cells in B5:B15 that do not contain the #N/A error or the #VALUE! error. However, this approach is tedious if the goal is to exclude all types of errors from the count. In that case, the SUMPRODUCT option below is more straightforward.
SUMPRODUCT function
The SUMPRODUCT function works directly with arrays and ranges. One thing you can do quite easily with SUMPRODUCT is perform a logical test on a range, then count the results. In this case, we want to count cells that do not contain errors and the simplest way to do that is with the ISERROR function and the NOT function. In the worksheet shown above, the formula in cell E5 is:
=SUMPRODUCT(--NOT(ISERROR(B5:B14)))
Working from the inside out, we first use the ISERROR function on the range B5:B14.
ISERROR(B5:B14) // check all 10 cells
Since there are ten cells in the range B5:B14, ISERROR returns an array with ten results like this:
{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
Here, each TRUE value indicates a cell value that is an error. Since the goal is to count cells that do not contain errors, we reverse these results with the NOT function :
NOT({FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE})
which returns a new array like this:
{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}
Notice that each TRUE value now corresponds to a cell that does not contain an error. This array is now in the correct form. However, SUMPRODUCT only works with numeric data so the next step is to convert the TRUE and FALSE values to their numeric equivalents, 1 and 0. We do this with a double negative (–):
--{TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE}
The resulting array looks like this:
{1;0;1;1;1;0;1;1;1;0}
Finally, SUMPRODUCT sums the items in this array and returns the total, which in the example is the number 3:
=SUMPRODUCT({1;0;1;1;1;0;1;1;1;0}) // returns 7
ISERR function
Like the ISERROR function, the ISERR function returns TRUE when a value is an error. The difference is that ISERR ignores #N/A errors. If you want to count cells that do not contain errors, and ignore #N/A errors, you can substitute ISERR for ISERROR:
=SUMPRODUCT(--NOT(ISERR(B5:B14))) // returns 8
SUMPRODUCT returns 8 since there are eight cells that do not contain errors, ignoring the #N/A error in cell B14.
Explanation
The goal in this example is to count cells in a range that do not contain a given number of strings. The cells to evaluate are in the named range data (B5:B14) and the strings to exclude are listed in the named range exclude (D5:D7). If your needs are simple, you can use the COUNTIFS function to solve this problem. In more complicated scenarios, you can use the SUMPRODUCT function in combination with ISNUMBER and SEARCH. In Excel 365, you can use the REDUCE function to streamline the formula somewhat. The formulas for all three approaches are below.
Note: This formula is complicated by the “contains” requirement. If you just need a formula to count cells that do not equal many things, you can use a more straightforward formula based on the MATCH function .
COUNTIFS function
If you have a limited number of strings to exclude, you can use the COUNTIFS function like this:
=COUNTIFS(data,"<>*pink*",data,"<>*orange*",data,"<>*black*")
This formula uses the not equal to operator (<>) with the asterisk (*) wildcard to count cells that do not contain each string anywhere. However, with this approach, you must enter a new pair of range/criteria arguments for each string to exclude. In contrast, the formula explained below can handle a large number of strings to exclude entered directly in a range on the worksheet.
MMULT function
In the example shown, the formula in cell F5 is:
{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(exclude),data))),ROW(exclude)^0)>0))}
This is a complex formula built around the MMULT function, which is designed to perform matrix multiplication. However, the core of the formula is based on the ISNUMBER and SEARCH functions:
ISNUMBER(SEARCH(TRANSPOSE(exclude),data))
Here, we transpose the items in the named range “exclude”, then feed the result to SEARCH as the find_text , with data provided as within_text . The SEARCH function returns a 2d array of TRUE and FALSE values, 10 rows by 3 columns, like this:
{3,#VALUE!,12;#VALUE!,4,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,3;14,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;#VALUE!,#VALUE!,#VALUE!;3,#VALUE!,12}
For each value in data , we have 3 results (one per search string) that are either #VALUE errors or numbers. Numbers represent the position of a found text string, and errors represent text strings not found. The TRANSPOSE function is needed to generate the 10 x 3 array of complete results.
This array is fed into ISNUMBER to get TRUE FALSE values, which we convert to 1s and 0s with a double negative (–) operator. The result is an array like this:
{1,0,1;0,1,0;0,0,0;0,0,0;0,0,1;1,0,0;0,0,0;0,0,0;0,0,0;1,0,1}
MMULT function
The array above is provided to the MMULT function as array1 . Following the rules of matrix multiplication, number of columns in array1 must equal the number of rows in array2 . To generate array2 , we use the ROW function like this:
ROW(exclude)^0
This yields an array of 1s, 3 rows by 1 column:
{1;1;1}
which goes into MMULT as array2 . After array multiplication, we have an array dimensioned to match the original data:
{2;1;0;0;1;1;0;0;0;2}
In this array, any non-zero number represents a value where at least one of the excluded strings has been found. Zeros indicate no excluded strings were found. To force all non-zero values to 1, we use greater than zero:
{2;1;0;0;1;1;0;0;0;2}>0
which creates yet another array of TRUE and FALSE values:
{TRUE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}
Our final goal is to count only text values where no excluded strings were found , so we need to reverse these values. We do this by subtracting the array from 1. This is an example of boolean logic . The math operation automatically coerces TRUE and FALSE values to 1s and 0s, and we finally have an array to return to the SUM function:
=SUM({0;0;1;1;0;0;1;1;1;0})
The SUM function returns a final result of 5.
REDUCE function
The REDUCE function , available in Excel 365 , offers a more straightforward way to solve this problem. REDUCE loops over a range of data and applies a custom calculation to each cell, aggregating results to a single value using the calculation of your choice. To solve this problem with REDUCE, you can use a formula like this:
=REDUCE(0,data,LAMBDA(a,b,a+NOT(SUM(--ISNUMBER(SEARCH(exclude,b))))))
The logic is similar to the formula described above, but simplified somewhat since we only need to check one cell at a time.
