How to Calculate Sales per Person and Product in Excel

Sales per person is a metric that measures how much revenue each salesperson generates for the business. It can be used to evaluate the performance and productivity of the sales team, as well as to identify areas for improvement. Sales per product is a similar metric that shows how much revenue each product or product category generates for the business. It can be used to analyze the demand and profitability of different products, as well as to optimize the product mix and pricing strategy.

In this article, we will show you how to calculate sales per person and product in Excel using two methods: the SUMIFS function and the PivotTable feature. We will also provide a sample workbook that you can download and use for your own data.

Method 1: Using the SUMIFS Function

The SUMIFS function in Excel allows you to sum values based on one or more criteria. For example, you can use the SUMIFS function to calculate the total sales of a specific person or product from a table of sales data. Here is the syntax of the SUMIFS function:

=SUMIFS (sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range is the range of cells that you want to sum.
• criteria_range1 is the range of cells that contains the first criteria.
• criteria1 is the first criteria that defines which cells to sum.
• criteria_range2, criteria2, etc. are optional additional ranges and criteria that you can specify.

To illustrate how to use the SUMIFS function, let’s use the following table of sales data as an example. The table contains the sales records of four salespeople (A, B, C, and D) and four products (X, Y, Z, and W) for the month of January.

To calculate the sales per person, we need to sum the sales values for each person. We can use the SUMIFS function to do this. For example, to calculate the sales of person A, we can use this formula:

=SUMIFS (F2:F17, D2:D17, "A")

• F2:F17 is the sum_range, which contains the sales values.
• D2:D17 is the criteria_range1, which contains the person names.
• "A" is the criteria1, which specifies that we only want to sum the sales of person A.

The result of this formula is 4700, which is the total sales of person A. We can copy and paste this formula to the other cells to get the sales of the other persons. Alternatively, we can use a cell reference instead of a hard-coded value for the criteria. For example, if we have a list of person names in column H, we can use this formula:

=SUMIFS (F2:F17, D2:D17, H2)

• H2 is the cell that contains the person name that we want to sum the sales for.

The result of this formula is the same as before, 4700. We can drag this formula down to the other cells to get the sales of the other persons. The final result looks like this:

To calculate the sales per product, we need to sum the sales values for each product. We can use the SUMIFS function again, but this time we change the criteria_range and criteria to match the product codes. For example, to calculate the sales of product X, we can use this formula:

=SUMIFS (F2:F17, B2:B17, "X")

• B2:B17 is the criteria_range1, which contains the product codes.
• "X" is the criteria1, which specifies that we only want to sum the sales of product X.

The result of this formula is 4200, which is the total sales of product X. We can copy and paste this formula to the other cells to get the sales of the other products. Alternatively, we can use a cell reference instead of a hard-coded value for the criteria. For example, if we have a list of product codes in column J, we can use this formula:

=SUMIFS (F2:F17, B2:B17, J2)

• J2 is the cell that contains the product code that we want to sum the sales for.

The result of this formula is the same as before, 4200. We can drag this formula down to the other cells to get the sales of the other products. The final result looks like this:

Method 2: Using the PivotTable Feature

Another way to calculate sales per person and product in Excel is to use the PivotTable feature. A PivotTable is a powerful tool that allows you to summarize, analyze, and present data from a table or range. You can use a PivotTable to group and aggregate data by different categories, such as person, product, date, etc. You can also create charts and slicers to visualize and filter the data.

To illustrate how to use the PivotTable feature, let’s use the same table of sales data as before. To create a PivotTable from this table, follow these steps:

1. Select any cell in the table, or select the entire table.
2. Go to the Insert tab, and click the PivotTable button in the Tables group.
3. In the Create PivotTable dialog box, choose where you want to place the PivotTable: in a new worksheet or in an existing worksheet. You can also change the data source if needed. Click OK.
4. In the PivotTable Fields pane, drag the fields that you want to use to the appropriate areas: Filters, Columns, Rows, and Values. For example, to calculate the sales per person, drag the Person field to the Rows area, and drag the Sales field to the Values area. To calculate the sales per product, drag the P. Code field to the Rows area, and drag the Sales field to the Values area. You can also drag the Product field to the Columns area to create a cross-tabulation of sales by person and product.
5. Adjust the formatting and layout of the PivotTable as desired. For example, you can change the number format, sort and filter the data, add totals and subtotals, etc.

The result of creating a PivotTable from the sales data looks like this:

As you can see, the PivotTable feature makes it easy to calculate sales per person and product in Excel.

Comparison and Analysis of the Two Methods

Both the SUMIFS function and the PivotTable feature are useful tools to calculate sales per person and product in Excel. However, they have some advantages and disadvantages that you should be aware of. Here is a comparison and analysis of the two methods:

• The SUMIFS function is a simple and flexible way to sum values based on one or more criteria. You can use it to calculate sales per person and product, as well as other metrics, such as average price, profit margin, etc. You can also use it to create custom formulas and calculations that suit your needs. However, the SUMIFS function has some limitations, such as:
  • You need to specify the criteria_range and criteria for each calculation, which can be tedious and error-prone if you have many criteria or a large data set.
  • You need to create separate formulas for each person and product, which can make your worksheet cluttered and hard to maintain.
  • You need to update the formulas manually if you add or remove data from the table, or if you change the criteria.
• The PivotTable feature is a powerful and interactive way to summarize, analyze, and present data from a table or range. You can use it to calculate sales per person and product, as well as other metrics, such as count, average, min, max, etc. You can also use it to create charts and slicers to visualize and filter the data. However, the PivotTable feature has some drawbacks, such as:
  • You need to create a separate PivotTable for each calculation, which can take up a lot of space and memory in your workbook.
  • You need to refresh the PivotTable manually or automatically if you change the data in the table, or if you want to see the latest results.
  • You need to be familiar with the PivotTable interface and options, which can be confusing and overwhelming for beginners.

In conclusion, both the SUMIFS function and the PivotTable feature are effective methods to calculate sales per person and product in Excel. The best method depends on your preference, skill level, and data size. You can also use both methods together to get the best of both worlds. For example, you can use the SUMIFS function to create a summary table of sales per person and product, and then use the PivotTable feature to create a chart and a slicer to explore the data further.