In every business, calculating total sales is very common. It is very important to calculate total sales to calculate total revenue in business. Some people may also call it gross sales. Here, in this article, I am sharing with you a free template to calculate total sales in Excel. So, you can easily download it and use it. Also, I will describe the steps how to calculate total sales in Excel. So, you can modify it easily according to your needs.
How to Calculate Total Sales in Excel: with Easy Steps
To calculate the total sales of a business in Excel, you must have some data ready. And you have to make a layout template for those data so you can extract the data to calculate the total sales. So, here I am describing all the necessary steps to calculate total sales in Excel.
Step 1: Make a List of Products in Excel
- First, create a new worksheet and input the product list data here. Each product must have a unique product code and a price of it.
- Now, you have to define the Name for the Product Code column cells. Because, when you will input data in the sales worksheet, you have to do specific entries of product codes. If you insert different codes for the same product, the formula can’t count them.
- So, to define the Name of the cells, select the cells first.
- Then, go to Formulas > Define Name.
- Then, a window will appear. Here, give a name for the list.
- And press OK.
Step 2: Record Sales in a New Worksheet
Now, you have to make a new worksheet where you will input the data of sales.
- First, make a layout of the necessary columns.
- Then, go to the Product Code column. You have to restrict the cells with Data Validation, so it won’t take any entry out of the Code list.
- For this, select the cells of the P. Code column and go to Data > Data Validation Tool
- Then, the Data Validation window will appear. Keep remaining in the Settings tab.
- Now, select List in the Allow option.
- Then, write “=code” in the Source box.
- Finally, press OK.
- As a result, you will see a drop-down option in each cell of this column. And, you have to select any of them. And without them, you can’t add any value to the cells.
- Now, enter the data into cells and create Sales Data for the time period.
Step 3: Calculate Total Sales in a New Worksheet
When Sales Data for a specific time period is ready, you can count the Total Sales of that period for each product. The steps are given below:
- First, input again the list of the products. And make a layout of the Total Sales worksheet.
- Then, insert this formula into cell D5 to calculate the total sales of the product.
=SUMIF(Sales!$D$5:$D$14,B5,Sales!E5:E14)
- Then, use the Fill Handle icon to drag the formula to the other cells of the icon, or use the Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste the formula.
🔎 Formula Breakdown:
Syntax of SUMIF function is-
=SUMIF(range, criteria, [sum_range])
- Range = Sales!$D$5:$D$14
These are the range of cells, that will be evaluated by criteria. We have taken the range D5 to D15 cells in the Sales Worksheet. Which is actually the column of the Product Code.
- Criteria = B5
The criteria is- it will be equal to the value of B5 in the active worksheet which contains the product code.
- [sum_range] = Sales!E5:E14
The actual cells to add if you want to sum cells other than those defined in the range argument. So, here it will take values in the range E5 to E14 in the Sales worksheet which contains the Unit Quantity sold. As a result, it will sum values from this range only that have met the criteria
- =SUMIF(Sales!$D$5:$D$14,B5,Sales!E5:E14)
It will sum the values from the Unit Qty column if the corresponding row value of the column P. Code meets the criteria which are to be equal to the value of the B5 cell in the active worksheet.
- Now, you have got the total sales of each product.
- Then, to calculate the total revenue from each product, insert this formula in cell E5.
=D5*Product!D5The formula will multiply the value of D5, which is the total sale value, with the cell D5 in the product worksheet which is the Unit Price of the product.
- Then, drag the Fill Handle icon to paste the formula to the other cells.
- Thus, you have got the total sales and total revenue for each product for a time period.
Download Sample Workbook
Conclusion
In this article, I have shown you how to calculate total sales in Excel. Also, I have shared the Excel template to calculate the total sales. In addition, you have found the steps to make a total sales template. So, you can easily modify it to use it as you need. With this template, you can calculate the total revenue. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.
<< Go Back to Sales | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
