Function Problems

Sum GT and ST Sales by Employee and Product in Excel

Olivia MillerOlivia Miller Nov 18, 2026 868 views

Question details

Calculate GT Act and ST Act sales totals for each employee and product category using data from the Raw Data worksheet.

Sum GT and ST Sales by Employee and Product in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A summary worksheet lists employees, while the Raw Data worksheet contains salesperson names, product descriptions, and sales amounts.
Observed behavior
The desired result is a separate total for each employee and sales measure, including only rows whose product description contains the chosen category.
Before you start

Identify the salesperson, product description, GT Act, and ST Act columns in Raw Data, along with the employee name cell on the summary sheet. The examples use columns G and H for salesperson and product, A2 for the employee, and M for an amount column; confirm these against your workbook.

Solution 1Recommended

Use SUMPRODUCT to match an employee and product category

Use this method when the product category appears within a longer product description.

1
Enter the first total

Select the GT Act total cell for the employee in A2 and enter =SUMPRODUCT(ISNUMBER(SEARCH("Zopper Extended Warranty",'Raw Data'!$H$2:$H$14))*('Raw Data'!$G$2:$G$14=A2)*'Raw Data'!$M$2:$M$14). Use column M only if it contains GT Act amounts.

2
Calculate the other measure

Select the corresponding ST Act total cell and enter the same formula, replacing 'Raw Data'!$M$2:$M$14 with the actual ST Act amount range.

3
Adjust and fill the formulas

Change the category text and row limits as needed, keeping all ranges the same size. Drag each formula's fill handle down to calculate totals for the remaining employees.

Use SUMPRODUCT to match an employee and product category
Check source values: SEARCH matches category text anywhere in the product description and is not case-sensitive. Amounts should be numeric, and the referenced cells should not contain errors.
Calculate sales totals

Calculate employee sales totals in WPS Spreadsheet

Open your workbook in WPS Spreadsheet and use SUMPRODUCT to total GT Act and ST Act amounts by employee and product category.

  1. 1. Open the workbook: Open WPS Spreadsheet, select Open, and choose the workbook containing the Raw Data and employee summary sheets.
  2. 2. Check the source columns: Select the Raw Data sheet tab and identify the salesperson, product description, GT Act, and ST Act columns.
  3. 3. Enter the GT Act formula: On the summary sheet, select the first GT Act total cell and enter =SUMPRODUCT(ISNUMBER(SEARCH("Zopper Extended Warranty",'Raw Data'!$H$2:$H$14))*('Raw Data'!$G$2:$G$14=A2)*'Raw Data'!$M$2:$M$14), adjusting the ranges to match your workbook.
  4. 4. Calculate ST Act and fill down: In the ST Act total cell, use the same formula with the ST Act amount range. Drag the fill handle down both total columns for the remaining employees.
Calculate separate employee totals for GT Act and ST ActMatch product categories within longer descriptionsOpen and save Microsoft Excel XLSX and XLS filesUse familiar spreadsheet formulas and fill controls
microsoft office alternative - wps office

Frequently Asked Questions

How do I calculate a total for a different product category?

Replace "Zopper Extended Warranty" in the SEARCH or SUMIFS formula with the category text you want to match.

Why does my formula return zero for an employee?

Check that A2 matches the salesperson name in Raw Data column G and that column H contains the category text. Also confirm that the amount range points to the correct sales column.

Can I use the same formula for both GT Act and ST Act?

Yes. Keep the employee and product ranges the same, but change the amount range to the column for the measure you want to total.

Why does SUMPRODUCT return an error?

Check that all referenced ranges cover the same number of rows and that the source cells do not contain errors. For example, use rows 2 through 14 for the salesperson, product, and amount ranges.