SUMPRODUCT with LEN One way to solve this problem is to use theSUMPRODUCT functionwith theLEN function. This array is then compared to cell E4, which contains the number 5. The result is a new array containing 11 TRUE and FALSE values. To summarize: Each TRUE value corresponds to a cell in B5:B15 that contains 5 characters. If a new number is entered in cell E4, the formula will recalculate and return a new result....

For convenience, the range B5:B15 isnameddata. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both methods are explained below. COUNTIF function TheCOUNT functioncounts the number of cells in a range that match the supplied criteria. COUNTIFS is in agroup of eight functionsthat share this syntax.

This problem can be solved with the COUNT function or the SUMPRODUCT function. Both methods are explained below. COUNT function TheCOUNT functioncounts the number of cells in a range that contain numeric values. Cell B9 is not included because it is empty. Cells B11 and B12 are not included because they containtext values. Note: theCOUNTA functioncounts numbers and text, but does not count empty cells. The main reason is flexibility and extensibility....

This can be done with the SUMPRODUCT function together with the ISODD function. Instead of ISODD, the MOD function can also be used. Both approaches are explained below. SUMPRODUCT with ISODD The SUMPRODUCT function works directly with arrays. ISODD is one of these functions. The + operator is an easy way to make ISODD return all results. We therefore test for 1. ISODD will return the #VALUE error if a value is not numeric....

For convenience, the range B5:B15 isnameddata. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both methods are explained below. COUNTIF function TheCOUNT functioncounts the number of cells in a range that meet supplied criteria. Note that cell B9 is excluded since it is equal to zero. Also notice that we supply thecriteriain double quotes (">0"). COUNTIF is part of agroup of Excel functionsthat split logical expressions into two parts....

Explanation In this example, the goal is to count cells thatcontaina specific substring. This problem can be solved with the SUMPRODUCT function or the COUNTIF function. Both approaches are explained below. The SUMPRODUCT version can also perform a case-sensitive count. COUNTIF function TheCOUNTIF functioncounts cells in a range that meet supplied criteria. To be included in the count, a cell must contain “apple” and only “apple”. If a cell contains any other characters, it will not be counted....

Empty cells and cells that contain numeric values or errors should not be included in the count. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both approaches are explained below. For convenience,datais thenamed rangeB5:B15. The asterisk (*) matches zero or more characters of any kind. We want to matchalltext values. To do this, we provide the asterisk (*) by itself for criteria. SUMPRODUCT function Another way to solve this problem is to use theSUMPRODUCT functionwith theISTEXT function....

Explanation In this example, the goal is to count cells thatdo not containa specific substring. This problem can be solved with theCOUNTIF functionor theSUMPRODUCT function. Both approaches are explained below. Although COUNTIF isnotcase-sensitive, the SUMPRODUCT version of the formula can be adapted to perform a case-sensitive count. For convenience,datais thenamed rangeB5:B15. COUNTIF function TheCOUNTIF functioncounts cells in a range that meet supplied criteria. To be included in the count, a cell must contain “apple” and only “apple”....

The best way to solve this problem is to use the SUMPRODUCT function together with the ISERROR function. you could also use the COUNTIF function or COUNTIFS function to exclude specific errors. Both approaches are explained below. Notice both values are enclosed in double quotes. 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....

In Excel 365, you’re free to 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. 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. Zeros indicate no excluded strings were found....

), asterisk(*), or tilde (~). A question mark (?) matches any one character and an asterisk (*) matches zero or more characters of any kind. The tilde (~) is an escape character to matchliteralwildcards that may appear in data. In this example, we mainly use an asterisk (*). Notice that COUNTIF isnotcase-sensitive. and asterisk (*) wildcards. Notice the hyphen (-) is hardcoded to make the pattern even more specific. COUNTIF can be used to count…

But we need a more advanced formula to count columns that may contain multiple instances of a specific value. MMULT option One option for solving this problem is theMMULT function. The MMULT function returns the matrix product of two arrays, sometimes called the “dot product”. The result from MMULT is anarraythat contains the same number of rows asarray1and the same number of columns asarray2. The MMULT function takes twoarguments,array1andarray2, both of which are required....

Explanation In this example, the goal is to count the maximum number of consecutive monthly orders. That is, we want to count consecutive monthly orders greater than zero. This is a tricky formula to understand, so buckle up! They key to the formula is knowing that theFREQUENCY functiongathers numbers into “bins” in a particular way. If so, IF returns the column number using theCOLUMN function. Those columns where the count is zero become FALSE....