How to Calculate Total Sales in Excel (With Easy Steps)

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 as of your need.


Download Sample Workbook

You can download the practice workbook from here:


Steps to Calculate Total Sales in Excel

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

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

Make a List of Products in Excel

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

Make a List of Products in Excel

  • Then, a window will appear. Here, give a name for the list.
  • And press OK.

Make a List of Products in Excel

Read More: How to Calculate Average Sales in Excel (5 Simple Approaches)


Similar Readings


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

Make a List of Products in Excel

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

Make a List of Products in Excel

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

Make a List of Products in Excel

  • Now, enter the data into cells and create Sales Data for the time period.

How to Calculate Total Sales in Excel

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


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.

Calculate Total Sales in a New 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)

Calculate Total Sales in a New Worksheet

🔎 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 which 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 met the criteria which are to be equal to the value of the B5 cell in the active worksheet.

Calculate Total Sales in a New 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!D5

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

Calculate Total Sales in a New Worksheet

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

Calculate Total Sales in a New Worksheet

Read More: How to Calculate Average Sales per Month in Excel (2 Easy Methods)


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 your need. With this template, you can calculate the total revenue also. 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.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo