Microsoft Office Tutorials and References
In Depth Information
Chapter 15: Performing Magic with Array Formulas
Figure 15-3: Using an array formula to determine whether a range contains a particular value.
Yet another approach uses the COUNTIF function in a non-array formula:
Counting the number of differences in two ranges
The following array formula compares the corresponding values in two ranges (named MyData
and YourData ) and returns the number of differences in the two ranges. If the contents of the
two ranges are identical, the formula returns 0.
The two ranges must be the same size and of the same dimensions.
This formula works by creating a new array of the same size as the ranges being compared. The
IF function fills this new array with 0s and 1s (0 if a difference is found, and 1 if the corresponding
cells are the same). The SUM function then returns the sum of the values in the array.
The following array formula, which is simpler, is another way of calculating the same result: