Learn more about counting data occurrences
The third example in the accompanying article “Spot complex duplicates based on multiple criteria” relies on an array formula to count the number of times a combination of data appears in a range. It may be hard to follow the formula, especially due to the confusing nature of how Excel processes array formulas. The best way to understand the formula is to walk through how it calculates a specific cell result. We’ll look at the formula used in cell G9:
{=SUM((A9=$A$2:A9)*(B9=B$2:B9)*(E9=E$2:E9))}
First, the formula evaluates the section (A9=$A$2:A9). Since this is an array formula, Excel compares the value in cell A9 (Aaron) to the eight cells in range A2:A9, as
