For example, the screen above shows two groups that contain five values each. Three values match and two are different. The formula used to count matching values is: Working from the inside-out, the B5:B9 is compared to C5:C9 in a simple expression that creates an array of five TRUE FALSE values: We want to count matches (TRUE values) but the SUMPRODUCT function will ignore non-numeric values, so we use a double unary to change the TRUE FALSE values to ones and zeros. This works because Excel automatically coerces TRUE/FALSE to 1/0 during math operations. The first negative changes TRUE values to -1, and FALSE values to 0. The second negative simply changes -1 to 1. The result inside SUMPRODUCT looks like this: With only one array to work with, SUMPRODUCT simply returns the sum of all items, 3. Author
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.
