Sum Transactions by Vendor with an Excel PivotTable
Question details
Summarize approximately 1,200 transactions by vendor and calculate each vendor's total without manual formulas.

- 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.
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.
Create an Excel PivotTable to total amounts by vendor
Use a PivotTable to group all transactions by vendor and calculate each vendor's total.
Select a cell in the transaction data and choose Insert > Table. Confirm the range and select My table has headers, then click OK.
Select a cell in the table, choose Insert > PivotTable, and confirm the table and report location. Click OK.
In the PivotTable Fields pane, drag Vendor to the Rows area and Amount to the Values area.
If the Values area shows Count of Amount instead of Sum of Amount, open the Amount field's Value Field Settings and choose Sum.
Drag Date to Filters to filter the report by date, or to Columns to break down the vendor totals by date.
Add new transactions to the source table, then right-click the PivotTable and choose Refresh to update the vendor totals.

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. 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. Insert a PivotTable: Select the transaction data and choose Insert > PivotTable. Confirm the source range and choose where to place the report.
- 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. 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.

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.




