Pivot Table Issues

Use an Excel PivotTable to Total Data by Company and Rate

Maira MehtabMaira Mehtab Nov 18, 2026 869 views

Question details

Summarize source data by company and rate, with totals displayed across eight or more variable categories.

Use an Excel PivotTable to Total Data by Company and Rate
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user wants to turn a source table into a summary of totals by company, rate, and multiple categories.
Observed behavior
The desired result is a summary showing totals for each relevant company, rate, and category combination.
Before you start

Check that each source column has a unique header and that the Total values are numeric. Identify whether the variable categories are values in one column or separate numeric columns.

Solution 1Recommended

Create a PivotTable for Company and Rate Totals

Use a PivotTable to group records and summarize totals without writing formulas.

1
Prepare the source table

Select the source data and choose Insert > Table. Confirm that the table has headers, including Company, Rate, and Total.

2
Insert a PivotTable

Click a cell in the table, choose Insert > PivotTable, select where to place it, and click OK.

3
Arrange the fields

In the PivotTable Fields pane, drag Company to Rows, Rate to Columns, and Total to Values. If Values shows Count of Total, open Value Field Settings and select Sum.

4
Add variable categories

If categories are stored in a separate Category column, drag Category to Columns beneath Rate. If each category has its own numeric column, drag those category fields to Values instead of Total to summarize them separately.

5
Refresh the summary

After changing or adding source records, right-click the PivotTable and select Refresh. Make sure new records are included in the source table.

Create a PivotTable for Company and Rate Totals
Avoid double-counting: If Total already includes the separate category amounts, use either Total or the category fields as needed rather than adding them together.
Summarize data with WPS Spreadsheets

Build Company and Rate Summaries in WPS Spreadsheets

Use a PivotTable in WPS Spreadsheets to group source records by company and rate, sum totals, and display additional categories.

  1. 1. Open the data: Open your workbook in WPS Spreadsheets and select a cell in the source data.
  2. 2. Insert a PivotTable: Choose Insert > PivotTable, confirm the data range and destination, and click OK.
  3. 3. Arrange the fields: Drag Company to Rows, Rate to Columns, and Total to Values. Set the value calculation to Sum if necessary.
  4. 4. Add categories and refresh: Drag a category field to Columns or separate numeric category fields to Values. After updating the source data, right-click the PivotTable and choose Refresh.
Group company and rate data in a PivotTableSummarize multiple numeric category fieldsOpen and edit Microsoft Excel XLS and XLSX filesUse a familiar spreadsheet interface
microsoft office alternative - wps office

Frequently Asked Questions

Why does my PivotTable count records instead of summing totals?

Check that the Total column contains numeric values. Then open Value Field Settings for the Total field and select Sum.

Can a PivotTable display eight or more categories?

Yes. Drag a Category field to Columns to display its category values, or add separate numeric category fields to Values to summarize each one.

How do I include newly added records in the PivotTable?

Add records within the source table, then right-click the PivotTable and select Refresh. If the source is a fixed range, update the data source to include the new rows before refreshing.

Can I summarize by company and rate without a PivotTable?

Yes. Use SUMIFS with the Total column as the sum range and the Company and Rate columns as criteria ranges. Add another criterion if you also need to filter by category.