In Excel, you can do any kind of sales-related calculation quite easily and speedily if you know some quick and smart methods. We are used to finding sales using the conventional summing method, but Excel has more tricky ways. I hope this article will cover all 4 smart methods to calculate annual sales in Excel with proper illustrations and sharp steps.
How to Calculate Annual Sales in Excel: 4 Useful Methods
Let’s get introduced to our dataset first which represents the monthly sales of a company.
1. Using Manual Formula to Calculate Annual Sales
In our very first method, we’ll learn the most common and basic method to calculate annual sales in Excel, just to add the cells using the plus sign (+). There is no need to use a function here. It’s not a feasible method to use always but for a short dataset, it may help in some particular cases.
- First, activate Cell C18 by clicking there.
- Then type the following formula in it-
- Later, just hit the Enter button to get the output.
Soon you will get the annual sales like the image below.
Read More: How to Calculate Projected Sales in Excel
2. Calculate Annual Sales Using Excel SUM Function
To find any kind of summation, we use the SUM function most. In the previous method, we had to select each cell separately to add but while using the SUM function, we can select the range at once.
- Write the following formula in Cell C18–
- After that, just press the Enter button to get the calculated annual sales.
Read More: How to Calculate Average Sales in Excel
3. Applying an Array Formula to Calculate Annual Sales
Here, I have modified my dataset a little bit. It represents the unit price and quantity on a monthly basis. So, before calculating the annual sales, we’ll have to find the monthly total by multiplying the unit price by quantity. We can perform it easily if we use an array formula with the SUM function. The array will multiply each unit price with the corresponding quantity and then the SUM function will sum them up.
- In Cell D18, insert the following formula-
- Lastly, hit the Enter button to finish.
Soon after, you will get the output like the image below.
💬 Note: I showed the method using Excel 365. As we used an array formula you may need to press Ctrl + Shift + Enter instead of pressing Enter to get the output in the earlier versions of Excel.
4. Applying Excel SUMPRODUCT Function
The same operation of the previous method can be done by using the SUMPRODUCT function. The array operation is included as arguments in the SUMPRODUCT function, so we do not need to use the array here separately.
- Type the following formula in Cell D18–
- Later, hit the Enter button to get the result.
Download Practice Workbook
I hope the procedures described above will be good enough to calculate annual sales in Excel. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.
- How to Calculate Total Sales in Excel
- How to Calculate Percentage of Sales in Excel
- How to Calculate Sales Mix with Formula in Excel
- How to Calculate Sales Conversion Rate Using Formula in Excel
- How to Use Sales Run Rate Formula in Excel
- Gross Sales Formula in Excel
- How to Use Sales Revenue Formula in Excel