Excel Video 354 has a practical example of an array formula. We’ll write a formula that loops through a range of cells to sum the collections if the year equals 2009. The array formula is made up of two parts that might be best understood from the inside out. The inside formula is an IF statement (Watch Excel Video 146 if you need a refresher on IF) that looks at cells A1:A15. If the value in those cells matches the value in cell E1, Excel returns the corresponding value from cells C1:C15. If not, Excel returns 0. The key is that as Excel loops through cells A1:A15, it returns the corresponding value. In other words, if A4 = E1, Excel returns C4. If A5 = E1, Excel returns C5. The SUM function adds up all of the results of the IF statement and presents the answer in one cell.
We’ll work through another example summing all of the collections for the North location in the video as well. Watch for a gotcha with array formulas. If you forget to press ctrl+shift+enter as you enter the formula, or if you forget to press ctrl+shift+enter when you edit the formula, Excel stops treating the formula as an array and you won’t get the results you expect.
There’s a similar function called SUMIF (discussed in Excel Video 184) that for comparison I’ve entered next to our array formulas. Array formulas have more flexibility than SUMIF. We’ll play with some of that flexibility in upcoming Excel Videos. I look forward to seeing you then.
