I hope you don’t have to spend a ton of time rewinding Excel Video 355. The array formula to sum multiple criteria at once is complicated. The SUMIFS formula in Excel Video 185 is a much easier approach to summing a range of data that meets multiple criteria. The reason we’ll jump through all of these hoops is so that in the next Excel Video we can sum using OR criteria (A OR B). SUMIFS can only deal with AND criteria (A AND B). If you want to sum all of the collections in 2009 OR the South location, you need to use an array.
The trick is to use TRUE and FALSE in equations. Excel treats TRUE as 1 and FALSE as 0. Once you know that, you can figure out that TRUE + FALSE = 1 and TRUE * FALSE = 0. We’ll use that math in arrays to calculate criteria, using * for AND criteria and + for OR criteria.
We’ll do another example next time to try to make this clearer. A big part of the reason to learn arrays is to be able to write complicated formulas when you need to. Don’t give up on arrays yet. We’ll do an OR example and then go on to other formulas you can’t get out of Excel without using arrays.
