Pinterest Pixel

Calculate Total Sales in Excel With An Array Formula

John Michaloudis
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.

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 in Excel.

If we had to calculate Total Sales in Excel 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!

Download excel workbookArray-Total-Sales.xlsx

Let’s do an example of an Array Formula to Calculate Total Sales in Excel for various products.

Follow the step-by-step guide below on How to calculate Total Sales in Excel:

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:

See also  How to Use EXP Function in Excel

=SUM(C13:C16*D13:D16)

Calculate Total Sales in Excel With An Array Formula

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)}

Calculate Total Sales in Excel With An Array Formula

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):

Calculate Total Sales in Excel With An Array Formula

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!

Further Learning:

 

If you like this Excel tip, please share it


See also  OFFSET Function Introduced

Founder & Chief Inspirational Officer

at

MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...