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

Using Manual Formula to Calculate Annual Sales in Excel

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.

Using Excel SUM Function to Calculate Annual Sales

Read More: How to Calculate Sales Growth over 5 Years in Excel (3 Ideal Examples)


Similar Readings


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.

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

Applying Excel SUMPRODUCT Function to Calculate Annual Sales


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

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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