{"id":2316,"date":"2020-06-26T20:39:15","date_gmt":"2020-06-26T18:39:15","guid":{"rendered":"https:\/\/www.myexcelonline.com\/?p=2316"},"modified":"2024-03-22T19:03:26","modified_gmt":"2024-03-22T18:03:26","slug":"calculate-total-sales-with-an-array-formula-excel","status":"publish","type":"post","link":"https:\/\/www.myexcelonline.com\/meo-staging\/blog\/calculate-total-sales-with-an-array-formula-excel\/","title":{"rendered":"Calculate Total Sales in Excel With An Array Formula"},"content":{"rendered":"

An Array Formula performs an Excel<\/a> operation (math, comparative, join, or function argument) on an array or range of data. \u00a0This could be a range of cells, a worksheet reference, or a defined name.<\/p>\n

One of the most popular uses of an array formula is to calculate Total Sales in Excel.<\/h4>\n

If we had to calculate Total Sales in Excel the normal way, we would have to create a “helper column<\/strong>” for the Totals column and then enter a formula to Sum all the Totals.<\/p>\n

Using an Array Formula we skip the “helper column” and just enter one formula only. \u00a0Quick and simple!<\/p>\n\n

Download excel workbook<\/span>Array-Total-Sales.xlsx<\/span><\/a><\/span><\/p>\n

Let’s do an example of an Array Formula to Calculate Total Sales in Excel for various products.<\/p>\n

Follow the step-by-step guide below<\/strong> on How to calculate Total Sales<\/strong>\u00a0in<\/a> Excel:<\/p>\n

STEP 1:<\/strong><\/span> Enter the SUM\u00a0formula =SUM(<\/strong><\/p>\n

STEP 2:<\/strong><\/span>\u00a0Select\u00a0the array\/range of data for the UNITS SOLD, enter the multiplier sign *<\/strong> and select the array\/range of the data for the UNIT PRICE:<\/p>\n

=SUM(C13:C16*<\/span><\/span>D13:D16<\/span>)<\/strong><\/p>\n

\"Calculate<\/a><\/p>\n

STEP 3:<\/strong><\/span> Instead of pressing ENTER to evaluate the formula, you need to press CTRL+SHIFT+ENTER<\/strong> to turn the formula into an Array Formula which will look like this:<\/p>\n

{=SUM(C13:C16*<\/span><\/span>D13:D16<\/span>)}<\/strong><\/p>\n

\"Calculate<\/a><\/p>\n

STEP 4:<\/strong> <\/span>By pressing F9 on the selected formula array<\/strong> 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):<\/p>\n

\"Calculate<\/a><\/p>\n

If we had to\u00a0get the above result using a non-Array Formula<\/a> we would have to create a helper column which multiplies UNITS SOLD by UNIT PRICE\u00a0and then enter the SUM\u00a0formula to get\u00a0the same result. \u00a0This is double the work!<\/p>\n

Further Learning:<\/strong><\/p>\n