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:

DOWNLOAD EXCEL WORKBOOK

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:

=SUM(C13:C16*D13:D16)

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:

{=SUM(C13:C16*D13:D16)}

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

HELPFUL RESOURCE:

This post was inspired by the book CTRL+SHIFT+ENTER: Mastering Excel Array Formulas

ctrlShiftEnter

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

email

Pin on Pinterest

Share on Facebook

Tweet about this on Twitter

Share on LinkedIn

Share on Google+

Related Posts

Concatenate With A Line Break Excel's CONCATENATE functions joins two or more text strings into one string.  The item can be a text value, number, or cell reference.If you add a double quotation with a space in between " " then this will add a space between the texts selected on either side.You ca...
Excel Subtotal Function – Avoid Double Count... What does it do?It returns a Subtotal in a list or databaseFormula breakdown:=SUBTOTAL(function_num, ref1)What it means:=SUBTOTAL(function number 1-11 includes manually-hidden rows & 101-111 excludes them, your list or range of data)***Go to the bottom...
IF Function: Introduction The IF function is probably one of the most used Excel functions because it is easy to understand and very flexible when you apply it to real life situations.Here I will show you a couple of ways that you can use the IF function to get you up and going.DOWNLOAD WORKBOOK ...
Getting the length of text with Excel’s LEN ... What does it do?Gives you the number of characters of the textFormula breakdown:=LEN(text)What it means:=LEN(text that you want to get the number of characters)There are times when you need to get the number of characters within a cell in Excel.  Thank...