Sum GT and ST Sales by Employee and Product in Excel
Question details
Calculate GT Act and ST Act sales totals for each employee and product category using data from the Raw Data worksheet.

- 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.
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.
Use SUMPRODUCT to match an employee and product category
Use this method when the product category appears within a longer product description.
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.
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.
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 SUMIFS with a wildcard for product descriptions
Use SUMIFS as a shorter alternative when you want to match a category within a product description.
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. Open the workbook: Open WPS Spreadsheet, select Open, and choose the workbook containing the Raw Data and employee summary sheets.
- 2. Check the source columns: Select the Raw Data sheet tab and identify the salesperson, product description, GT Act, and ST Act columns.
- 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. 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.

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.




