How to Calculate Annual Sales in Excel (4 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.

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.

Using Manual Formula to Calculate Annual Sales in Excel

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.

Steps:

  • Write the following formula in Cell C18–
=SUM(C5:C16)
  • After that, just press the Enter button to get the calculated annual sales.

Using Excel SUM Function to Calculate 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.

Applying an Array Formula to Calculate Annual Sales

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


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.

Applying Excel SUMPRODUCT Function to Calculate Annual Sales


Download Practice Workbook


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo