Sum Duplicate Vendor Entries by Month or Year with SUMPRODUCT
Question details
Calculate a total for a vendor and matching month or year when the vendor appears in multiple rows.

- Product
- Microsoft Excel and WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A forecast table contains vendor names in rows, periods in column headers, and values to summarize on another sheet.
- Observed behavior
- The desired result is a total that includes every matching vendor row and the column with the matching period.
Check that vendor names are in Forecast Entry!A2:A5, period headers are in B1:D1, and numeric values are in B2:D5. On SAP Upload, place vendor names in row 2 and matching period labels in column A.
Use SUMPRODUCT to total matching vendors and periods
Use this formula when vendors can appear in multiple rows and periods are stored as column headers.
The formula checks each vendor row and period column, then adds the values where both conditions match. The period label must match the source header, including its underlying date or text value.
Open the SAP Upload sheet and select cell B3, where the vendor in B2 and the period in A3 intersect.
In B3, enter =SUMPRODUCT(('Forecast Entry'!$A$2:$A$5='SAP Upload'!B$2)*('Forecast Entry'!$B$1:$D$1='SAP Upload'!$A3)*'Forecast Entry'!$B$2:$D$5) and press Enter.
Drag the fill handle from B3 down and across the result table. Adjust the source ranges in the formula if your forecast covers different rows or columns.

Total duplicate vendor entries in WPS Spreadsheets
Use SUMPRODUCT in WPS Spreadsheets to match vendor names and period headers, then fill the formula across your summary table.
- 1. Open the workbook: In WPS Spreadsheets, choose Open and select the workbook containing the Forecast Entry and SAP Upload sheets.
- 2. Check the table layout: On Forecast Entry, check the vendor names in A2:A5, period headers in B1:D1, and values in B2:D5. On SAP Upload, check the vendor in B2 and period in A3.
- 3. Enter SUMPRODUCT: Select SAP Upload!B3 and enter =SUMPRODUCT(('Forecast Entry'!$A$2:$A$5='SAP Upload'!B$2)*('Forecast Entry'!$B$1:$D$1='SAP Upload'!$A3)*'Forecast Entry'!$B$2:$D$5), then press Enter.
- 4. Copy the formula: Drag B3's fill handle down and across to calculate totals for the other periods and vendors.

Frequently Asked Questions
Will SUMPRODUCT include every duplicate vendor row?
Yes. The formula checks every name in Forecast Entry!A2:A5 and adds values from all rows that match the vendor in SAP Upload!B2 and the selected period.
Why does the formula return zero when I expect a match?
Compare the vendor name in SAP Upload!B2 with the names in Forecast Entry!A2:A5, and compare A3 with the period headers in B1:D1. Extra spaces or different underlying date and text values can prevent a match.
Can I use a year to match monthly date headers?
The example uses exact matches, so a year label will not automatically match all dates in that year. Use matching year headers or a different formula that tests the year of each date.
How do I extend the formula to more vendors or periods?
Edit the ranges in the formula bar. Extend the vendor and value ranges to the same last row, and extend the period and value ranges to the same last column.




