Md. Sourov Hossain Mithun
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 the 4 smart methods to calculate annual sales in Excel with proper illustrations and sharp steps.
Download Practice Workbook
You can download the free Excel workbook from here and practice on your own.
4 Ways to Calculate Annual Sales in Excel
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 the basic method to calculate annual sales in Excel, just to add the cells using plus sign (+). There is no need of using a function here. It’s not a feasible method to use always but for a short dataset, it may help in some particular cases.
Steps:
- First, activate Cell C18 by clicking there.
- Then type the following formula in it-
=C5+C6+C7+C8+C9+C10+C11+C12+C13+C14+C15+C16- 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 Total Sales in Excel (With Easy Steps)
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.
Steps:
- Write the following formula in Cell C18–
=SUM(C5:C16)- After that, just press the Enter button to get the calculated annual sales.
Read More: How to Calculate Sales Growth over 5 Years in Excel (3 Ideal Examples)
Similar Readings
- How to Forecast Sales Growth Rate in Excel (6 Methods)
- Calculate Sales Growth Percentage in Excel (2 Easy Approaches)
- How to Calculate Percentage of Sales in Excel (5 Suitable Methods)
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 monthly basis. So, before calculating the annual sales, we’ll have to find the monthly total by multiplying the unit price with 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.
Steps:
- In Cell D18, insert the following formula-
=SUM(C5:C16*D5:D16)- 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 so you may need to press Ctrl + Shift + Enter instead of pressing Enter to get the output in the earlier versions of Excel.
Read More: How to Calculate Average Sales per Month in Excel (2 Easy Methods)
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.
Steps:
- Type the following formula in Cell D18–
=SUMPRODUCT(C5:C16,D5:D16)- Later, hit the Enter button to get the result.
Conclusion
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.
