A very interesting and useful way of working with array formulas, is to combine them with Excel’s IF function.
Remember, the IF function works like this.
IF (test condition, value if test is true, value if test is false)
To begin, suppose you have two cells, B4 and C4, and we construct an IF function as follows.
=IF(B4=C4,B4, 0)
This function will test if the content of cell B4 is the same as the content of cell C4. If they are the same, the value in cell B4 will be returned. If they are not equal, a value of zero will be returned.
Now suppose we use ranges of cells (arrays), instead of individual cells.
=IF(B4:B14=C4:C14 B4:B14, 0).
If we make this an array formula (Ctrl + Shift +Enter), the IF function will return the value in the B4:B14 array whenever that value is equal to the corresponding value in the C4:C14 array. When the values are not equal, zero will be returned.
The next step is to combine this with a Sum function in the following way.
{=Sum (IF(B4:B14=C4:C14,B4:B14,0))}
If we make this an array formula, the function will basically step through both ranges (arrays) row by row, and Sum the values that are returned from the IF function.
The values returned from the If function are 0, 0, 0, 4, 5, 0, 0, 0, 4, 12, 0, for a result of 25.
Now, if we edit the formula to read {=Sum (IF (B4:B14=C4:C14, 1, 0))}, the IF function will return a one every time there are matching numbers, and a zero otherwise. The sum of these ones and zeros will result in a count of the matching items in both arrays.
Suppose we have a worksheet like the following.
If you wanted to find out how many hours Janet worked, you could create the following array formula.
{=SUM(IF(B2:B16="Janet",D2:D16,0))}
|
