An Array Formula performs an Excel operation (math, comparative, join or function argument) on an array or range of data.  This could be a range of cells, a worksheet reference or a defined name.

One of the most popular uses of an array formula is to calculate Total Sales.

If we had to calculate Total Sales the normal way, we would have to create a “helper column” for the Totals column and then enter a formula to Sum all the Totals.

Using an Array Formula we skip the “helper column” and just enter one formula only.  Quick and simple!

Let’s do an example of an Array Formula that calculates the Total Sales for various products:


STEP 1: Enter the SUM formula =SUM(

STEP 2: Select the array/range of data for the UNITS SOLD, enter the multiplier sign * and select the array/range of the data for the UNIT PRICE:


array sum

STEP 3: Instead of pressing ENTER to evaluate the formula, you need to press CTRL+SHIFT+ENTER to turn the formula into an Array Formula which will look like this:


array formula bar

STEP 4: By pressing F9 on the selected formula array will give you the resulting array of numbers (press CTRL+Z to get out of this mode when you are done checking the formula results):

array f9

If we had to get the above result using a non-Array Formula we would have to create a helper column which multiplies UNITS SOLD by UNIT PRICE and then enter the SUM formula to get the same result.  This is double the work!

Have a look at the following tutorial on the two methods:

Array Total Sales


How to Combine VLOOKUP and IFERROR to Replace the #N/A Error in Excel


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn


Share on Google+


Related Posts

Index Match 2 Criteria with Data Validation We can use the INDEX-MATCH formula and combine it with Data Validation drop down menus to return a value based on 2 criteria. This is a little advanced so you will need to drop what you are doing and really focus.  Let's go... First we need to convert our data into an Excel...
IFERROR Function: Introduction   What does it do? It returns a value that you set if a formula has an error Formula breakdown: =IFERROR(Value,Value if Error) What it means: =IFERROR(The Formula,What do you want to show if The Formula has an error?) If you have a calculation tha...
Two Way Lookup Using The SUM Intersect Function There are various lookup functions that bring back values from a range of data like the VLOOKUP, INDEX, INDEX-MATCH and OFFSET functions, just to name a few. There is another lookup formula that you can use that will return the intersection of two Named Ranges! This is done...
WEEKDAY function: Introduction   What does it do? Returns the day of the week corresponding to a date.  The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default. Formula breakdown: =WEEKDAY(Serial_Number, ) What it means: =WEEKDAY(Date, ) The WEEKDAY f...