where things is the named range E5:E7. Note: COUNTIF is not case-sensitive. The COUNTIF function counts the number of cells in a range that meet criteria. When you give COUNTIF a range of cells as the criteria, it returns an array of numbers as the result, where each number represents the count of one thing in the criteria range. In this case, the named range things (D5:D7) contains 3 values, so COUNTIF returns 3 results in an array as shown below: Since “apple” appears twice, “pears” appears three times, and “kiwis” appears once, the array contains the numbers 2, 3, and 1. This array is returned directly to the SUMPRODUCT function: With a single array to process, SUMPRODUCT simply sums the array and returns 6.
With an array constant
With a limited number of values, you can use an array constant in your formula like this:
ISNUMBER and MATCH
The above formula works fine, but has some limitations due to the nature of COUNTIF. As an alternative, you can use the formula below, which uses the ISNUMBER function with the MATCH function to achieve the same result: This is a more flexible formula in cases where logical conditions become more complex. It’s also useful when you need to extract a value from a range in the data to use in a condition.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
