Using Array Constants in your formulas

I find I use this kind of formula a lot…


Instead of:


… and imagine if you want to test 20 items for B5 is not equal to?  It’s shorter and easier to read and edit.  I discovered this by just playing around way back but it turns out these are called Array Constants and it can make many formulas simpler.



will count instances of TypeA OR TypeB in the A1:A10 range.  If say you enter TypeA and TypeB in that range with some other values the COUNTIFS part of this formula returns an array of {1,1} for the two instances found and then the SUM() formula wraps COUNTIFS to convert the array values to a value it can put into a cell so adds the 1 + 1 to return what you want which is 2.

You can also use Array Constants for simplifying nested IF statements which is an adjunct to an earlier post I did of Nested IFs (and avoiding them).

Here is a great example from where A1:A20 has values of 1,2,3…20 and this formula is entered to B1:B20



It categorizes values of 1-20 into groupings.  That’s a really practical use and example.

You might have not noticed the LOOKUP function before apart from it’s regular use (Office Support defines this as: Use LOOKUP, one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column), it’s ideal for looking up lookup vectors and result vectors within the actual formulas.  If you can’t recall from high school maths, vectors are 1-dimensional arrays so {1,6,11,16} is one vector and {“1-5″,”6-10″,”11-15″,”16-20”} is another.

So when you have a complex formula because it’s got a lot of testing to do be aware of array constants as a simpler way.  OK as with any constants there is a danger the values in the array constants may need to change so the poor user coming along after you has to find these buried in the formula to make a change.  But some values in practical situations rarely change, like this old chestnut for bean counters of grouping months into quarters…

=LOOKUP(A1,{1,4,7,10},{“Qtr 1″,”Qtr 2″,”Qtr 3″,”Qtr4”})

Get’s you thinking… there are lots of ways to use Array Constants if you have an awareness for it.