Microsoft Office Tutorials and References

In Depth Information

{=SUM(1*ISNA(MATCH(MyList,Master,0)))}

To return the first invalid item in
MyList
, use the following array formula:

{=INDEX(MyList,MATCH(TRUE,ISNA(MATCH(MyList,Master,0)),0))}

Figure 15-6:
Using an array formula to count and identify items that aren't in a list.

Summing the digits of an integer

I can't think of any practical application for the example in this section, but it's a good demonstration of the po-

tential power of an array formula. The following array formula calculates the sum of the digits in a positive in-

teger, which is stored in cell A1. For example, if cell A1 contains the value 409, the formula returns
13
(the sum

of 4, 0, and 9).

{=SUM(MID(A1,ROW(INDIRECT(“1:”&LEN(A1))),1)*1)}

To understand how this formula works, start with the ROW function, as shown here: