Track Total Sales for an Item Across Excel Worksheets
Question details
Calculate total sales for a single product when order information is currently stored on separate worksheets.
- Product
- Microsoft Excel workbook
- Device & OS
- not provided
- Scenario
- The user needs to report sales by item across multiple order or invoice worksheets.
- Observed behavior
- Sales data split across individual worksheets is difficult to summarize and maintain with formulas.
Identify the fields needed for each invoice line, such as invoice number, item name, quantity, and unit price. Use consistent item names and numeric values for quantity and price before creating a summary.
Consolidate invoice lines and calculate item sales with SUMIFS
Use this method when you want a formula-based total that updates as new invoice lines are added to one central table.
Keeping all order lines in one structured table makes reporting more reliable than referencing many separate worksheets. Add a Total column for each line, then use SUMIFS to total sales for the selected item.
Add a worksheet named Sales Data and create columns for Invoice Number, Item, Quantity, Unit Price, and Total. Copy each invoice line from the separate worksheets into this table, with one row per item sold.
In the Total column, enter a formula such as =C2*D2, where C is Quantity and D is Unit Price. Fill the formula down for all invoice lines.
Select the consolidated data range, then choose Insert > Table. Confirm that My table has headers is selected, and give the table a clear name such as SalesTable.
On a summary worksheet, type the product name to report in a cell, such as H2. Make sure it matches the item name used in the Item column.
In the result cell, enter =SUMIFS(SalesTable[Total],SalesTable[Item],H2). The formula adds the Total values for all rows whose Item equals the product entered in H2.
Summarize sales by item with a PivotTable
Use a PivotTable when you need totals for all products at once and want to filter results by invoice or other fields.
Create Item Sales Reports with WPS Spreadsheet
WPS Spreadsheet lets you organize invoice lines in one table and calculate product sales with SUMIFS or a PivotTable. Its familiar spreadsheet tools make it easy to migrate an existing workbook and maintain an up-to-date sales summary.
- 1. Open the sales workbook: Open the existing Excel workbook in WPS Spreadsheet and add a new worksheet for the consolidated sales data.
- 2. Create sales-data columns: Enter headers for Invoice Number, Item, Quantity, Unit Price, and Total, then place each invoice line on its own row.
- 3. Calculate line totals: In the Total column, enter =QuantityCell*UnitPriceCell and fill the formula down through the sales records.
- 4. Calculate product revenue: On a report sheet, enter the product name and use =SUMIFS(TotalRange,ItemRange,ItemCell) to return its total sales.
- 5. Build a PivotTable when needed: Select the data table, choose Insert > PivotTable, then place Item in Rows and Total in Values to view totals for all products.
Frequently Asked Questions
Can I total one item from several Excel worksheets without consolidating the data?
You can build formulas that reference each worksheet individually, but they become difficult to maintain as sheets are added or renamed. A single consolidated table is the more reliable option for ongoing reporting.
What columns should a sales table include?
At minimum, include Invoice Number, Item, Quantity, Unit Price, and Total. You can also add Order Date, Customer, Salesperson, or Region if you need filtering and more detailed reports.
Why does SUMIFS return zero for an item that has sales?
Check that the item name in the criteria cell exactly matches the Item column. Remove leading or trailing spaces, confirm that the SUMIFS sum range contains numeric Total values, and make sure the criteria and sum ranges have matching row counts.
How do I calculate total sales if I only have quantity and unit price?
Add a Total column with a formula such as =C2*D2, where C2 is Quantity and D2 is Unit Price. Then use SUMIFS on that Total column for the selected item.
Should I use SUMIFS or a PivotTable for sales reporting?
Use SUMIFS for a focused report that returns the total for a selected product. Use a PivotTable when you need a list of totals for many items and want to filter or group the data interactively.
