Step 1- Create a List of Products in Excel
- Create a new worksheet and enter the product list, the product code and its price.
- Select the cells to define a Name for the Product Code column.
- Go to Formulas > Define Name.
- Enter a name in the window.
- Click OK.
Step 2 -Save the Sales in a New Worksheet
- Open a new sheet and insert the necessary columns.
- Go to the Product Code column.
- To restrict the cells: select them and go to Data > Data Validation
- In the Data Validation window, select Settings.
- Choose List in Allow.
- Enter “=code” in the Source box.
- Click OK.
A drop-down list will be displayed in every cell in this column.
- Enter Sales Data.
Step 3 – Calculate the Total Sales in a New Worksheet
- Enter the list of products.
- Enter this formula in D5.
=SUMIF(Sales!$D$5:$D$14,B5,Sales!E5:E14)
- Use the Fill Handle to drag the formula to the other cells or press Ctrl+C and Ctrl+V to copy and paste the formula.
Formula Breakdown:
The syntax of the SUMIF function is:
=SUMIF(range, criteria, [sum_range])
- Range = Sales!$D$5:$D$14
Defines the range: D5:D15 in the Sales Worksheet (Product Code).
- Criteria = B5
Declares the value of B5 as the criterion.
- [sum_range] = Sales!E5:E14
Sum values that meet the criteria in this range.
- =SUMIF(Sales!$D$5:$D$14,B5,Sales!E5:E14)
Sums the values in the Unit Qty column if the corresponding row value of the column P. Code meets the criteria.
The total sales of each product is displayed.
- To calculate the total revenue of each product, enter this formula in E5.
=D5*Product!D5The formula will multiply the value of D5 (total sale value) by the D5 in the product worksheet (the Unit Price).
- Use the Fill Handle to drag the formula to the other cells
The total sales and the total revenue of each product are displayed.
Download Sample Workbook
<< Go Back to Sales | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
