Function Problems

Sum Duplicate Vendor Entries by Month or Year with SUMPRODUCT

Steven ParkerSteven Parker Nov 13, 2026 870 views

Question details

Calculate a total for a vendor and matching month or year when the vendor appears in multiple rows.

Sum Duplicate Vendor Entries by Month or Year with SUMPRODUCT
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the first result cell

Open the SAP Upload sheet and select cell B3, where the vendor in B2 and the period in A3 intersect.

2
Enter the formula

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.

3
Fill the results

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.

Use SUMPRODUCT to total matching vendors and periods
Keep ranges aligned: The vendor range must cover the same rows as the value range, and the period range must cover the same columns. A year label will match a year header; it will not automatically match every date within that year.
Work with spreadsheet formulas

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. 1. Open the workbook: In WPS Spreadsheets, choose Open and select the workbook containing the Forecast Entry and SAP Upload sheets.
  2. 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. 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. 4. Copy the formula: Drag B3's fill handle down and across to calculate totals for the other periods and vendors.
Sum all matching vendor rows for a selected period.Open and edit Microsoft Excel XLSX and XLS files.Fill formulas across a summary table using the fill handle.Work with a familiar spreadsheet interface.
microsoft office alternative - wps office

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.