Function Problems

Calculate Excel Stock Totals by Product and Store with SUMPRODUCT

Algirdas Jasaitis Nov 13, 2026 869 views

Question details

Calculate the total stock for a product in a specified store without manually entering row numbers in an INDEX formula.

Calculate Excel Stock Totals by Product and Store

Product: Microsoft Excel
Device & OS: not provided
Scenario: Stock quantities are arranged by product in rows and store in columns.
Observed behavior: The desired result is the sum of quantities matching product CERMAZ000-149008 and store W258.

Before you start

Check that product codes are in B4:B9, store codes are in C3:E3, and numeric stock quantities are in C4:E9. Adjust the ranges in the formula if your worksheet uses different cells.

Solution 1Recommended

Use SUMPRODUCT to Match the Product and Store

Use this formula to sum matching stock quantities without finding a row number or using INDEX and TEXTJOIN.

Select a result cell

Click an empty cell where you want the stock total to appear.

Enter the formula

Type =SUMPRODUCT((C4:E9)*(C3:E3="W258")*(B4:B9="CERMAZ000-149008")) and press Enter.

Check the ranges

In the formula bar, confirm that the stock range has the same rows as the product range and the same columns as the store-header range.

Use SUMPRODUCT to Match the Product and Store

When the data grows: The formula sums all matching product rows and store columns within the specified ranges. Extend the ranges if new data is added outside them, and keep stock cells numeric.

Solution 2

Create a Refreshable Pivot Table with Power Query

Use this approach when you need to filter and report on multiple products and stores.

Calculate stock in WPS Spreadsheets

Calculate Product and Store Stock Totals in WPS Spreadsheets

Use SUMPRODUCT in WPS Spreadsheets to calculate stock totals from matching product rows and store columns.

1. Open the workbook: Open WPS Spreadsheets and select Open to load your stock workbook.
2. Check the layout: Verify that product codes are in B4:B9, store headers are in C3:E3, and stock quantities are in C4:E9, or adjust the formula ranges to match your sheet.
3. Calculate the total: Click an empty cell, enter =SUMPRODUCT((C4:E9)*(C3:E3="W258")*(B4:B9="CERMAZ000-149008")), and press Enter.
4. Save the workbook: Choose File > Save or Save As to keep the result in your workbook.

Calculate stock totals without manually entering row numbersOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interface for formulas and tablesWork with spreadsheet files using the free WPS Office suite

Frequently Asked Questions

Do I need INDEX or TEXTJOIN to calculate the stock total?

No. SUMPRODUCT can match the product and store and sum the corresponding quantities directly.

What happens if a product or store appears more than once?

The SUMPRODUCT formula adds all quantities within the specified ranges that match both the product code and store code.

Will the formula include new products added below row 9?

Not automatically. Edit the stock and product ranges in the formula bar so they include the new rows, while keeping their row counts aligned.

How do I update the pivot table after changing stock data?

Make sure the changed data is included in the source table, then choose Data > Refresh All. Check the Power Query steps if the table layout has changed.

Excel & Data Problems