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:
=SUM(C13:C16*D13:D16)
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)}
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):
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:
- Total Bonus Due With An Array Lookup Formula
- Excel Array Formulas Explained
- Autosum an Array of Data in Excel
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 MyExcelOnline Academy Online Course.