Pivot Table Issues

Sum Transactions by Vendor with an Excel PivotTable

Nimra MalikNimra Malik Nov 18, 2026 869 views

Question details

Summarize approximately 1,200 transactions by vendor and calculate each vendor's total without manual formulas.

Automatically Sum Transactions by Vendor in an Excel PivotTable
Product
Microsoft Excel
Device & OS
not provided
Scenario
The transaction list contains recurring and one-time vendors, with an amount recorded for each transaction.
Observed behavior
The desired result is one row per vendor showing the sum of its transaction amounts.
Before you start

Make sure the transaction data has a header row with Vendor and Amount columns, and that amounts are stored as numbers. Use consistent vendor names so transactions for the same vendor are grouped together.

Solution 1Recommended

Create an Excel PivotTable to total amounts by vendor

Use a PivotTable to group all transactions by vendor and calculate each vendor's total.

1
Prepare the source data

Select a cell in the transaction data and choose Insert > Table. Confirm the range and select My table has headers, then click OK.

2
Insert a PivotTable

Select a cell in the table, choose Insert > PivotTable, and confirm the table and report location. Click OK.

3
Group transactions by vendor

In the PivotTable Fields pane, drag Vendor to the Rows area and Amount to the Values area.

4
Check the calculation

If the Values area shows Count of Amount instead of Sum of Amount, open the Amount field's Value Field Settings and choose Sum.

5
Add a date field if needed

Drag Date to Filters to filter the report by date, or to Columns to break down the vendor totals by date.

6
Refresh after adding transactions

Add new transactions to the source table, then right-click the PivotTable and choose Refresh to update the vendor totals.

Create an Excel PivotTable to total amounts by vendor
Table expansion and refresh: An Excel Table can expand to include new rows, but the PivotTable still needs to be refreshed to show the new transactions.
Summarize transactions in WPS Spreadsheets

Total vendor transactions with a WPS PivotTable

Use WPS Spreadsheets to create a PivotTable that groups transactions by vendor and sums their amounts. Refresh the report when the source data changes.

  1. 1. Open the transaction file: Open WPS Spreadsheets and use Open to select your Excel workbook. Check that the data has Vendor and Amount headers.
  2. 2. Insert a PivotTable: Select the transaction data and choose Insert > PivotTable. Confirm the source range and choose where to place the report.
  3. 3. Arrange the fields: In the PivotTable field list, drag Vendor to Rows and Amount to Values. Set the Amount value field to Sum if it shows Count.
  4. 4. Update the report: After editing the source data, right-click the PivotTable and choose Refresh. If new rows are outside the original source range, update the PivotTable's data source to include them.
Group recurring and one-time vendors in a single reportCalculate vendor totals without manual formulasOpen and save Microsoft Excel formats, including XLSXAdd a date field to filter or break down totals
microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable count amounts instead of summing them?

The Amount column may contain text or blank values. Make sure the amounts are numeric, then open the Amount field's Value Field Settings and select Sum.

How do I include new transactions in the vendor totals?

Add the transactions to the source Excel Table, then right-click the PivotTable and choose Refresh. If you used a fixed range instead, update the PivotTable's data source to include the new rows before refreshing.

Can I see vendor totals for a specific date?

Drag the Date field to the Filters area of the PivotTable Fields pane, then use the report filter to select the dates you want.

Why does the same vendor appear on more than one row?

Different spellings or extra spaces in the Vendor column can create separate groups. Correct the names in the source data, then right-click the PivotTable and choose Refresh.