The result is 9 because there are nine values in the range B5:B15 that match values in D5:D15 in corresponding rows.
Note: this formula counts matches in corresponding rows. To count all matches in two ranges, regardless of row, see this example.
In this example, the goal is to compare two columns and return the count of matches in corresponding rows. A good way to solve this problem is to use the SUMPRODUCT function or the SUM function, as explained below.
The SUMPRODUCT function is a versatile function that handles array operations natively without any special array syntax. Its behavior is simple: it multiplies, then sums the product of arrays. Working from the inside out, we compare the range B5:B15 to D5:D15 like this:
Because there are 11 values in the first range, the result is an array with 11 TRUE and FALSE values like this:
In this array, the TRUE values correspond to cells in B5:B15 that match corresponding cells in D5:D15. In this state, SUMPRODUCT will actually return zero because TRUE and FALSE values are not counted as numbers in Excel by default. To get SUMPRODUCT to treat TRUE as 1 and FALSE as zero, we need to "coerce" them into numbers. The double negative (--) is a simple way to do that:
This results in an array containing only 1s and 0s, which is returned directly to the SUMPRODUCT function:
In this example, the goal is to count orders where the color is "blue" and the quantity is greater than 15. All data is in the range B5:B15. There are two primary ways to solve this problem, one with the COUNTIFS function, the other with the...
In this example, the goal is to count the number of items sold and remaining, based on the data visible in columns B and C. The ID column holds unique ids, and the Sold column is used to record a sale. An "x" in the Sold column indicates the item...
In this example, the goal is to count the number of exact matches in two ranges, ignoring the sort order or location of the values in each range. This problem can be solved with the COUNTIF function or with the MATCH function. Each approach is...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.