More Useful Array Constant Formulas

I have been recently blogging on Array Constants (see below links) and how useful they are for simpler, more maintainable and readable formulas.

Also I often use SUMIFS which from the top of my head came with Excel 2007.  I did a job a few years ago that had another useful application of an Array Constant, this time incorporated with a SUMIFS to provide for an OR operation.

So making up some data to illustrate this consider the data I have shown below for fruit sales in Australia.  Say I want sales for Oranges in 3 states, Queensland, New South Wales and Victoria but only if sales are greater than $10,000.

In F4 in the example below,  I can use this SUMIFS formula wrapped in SUM with an Array Constant for ONE of the criteria of an SUMIFS formula (doesn’t have to be the first criteria as the F5 alternative formula example shows.

=SUM(SUMIFS(D2:D20,B2:B20,{“Queensland”,”New South Wales”,”Victoria”},C2:C20,”Oranges”,D2:D20,”>10000″))

Which is great to use as it simplifies the alternative SUMIFS formula that would otherwise look like this:

=SUMIFS(D2:D20,B2:B20,”Queensland”,C2:C20,”Oranges”)+SUMIFS(D2:D20,B2:B20,”New South Wales”,C2:C20,”Oranges”)+SUMIFS(D2:D20,B2:B20,”Victoria”,C2:C20,”Oranges”)-SUMIFS(D2:D20,D2:D20,”<=10000″)

Note I said you can only use the Array Constant for ONE of the criteria, wherever you place this in the SUMIFS formula.  It won’t let you use another Array Constant for a 2nd or more criteria.  I am not sure why, but hey I am grateful we can use it for the one criteria!  Given this, I use this method where you have the most, or the longest or most tedious criteria so the end formula is much simpler, more maintainable and more readable.

Array Constant SUMIFS example