How to Sum Invoice Amounts by Customer or Phone Number in WPS Office Spreadsheets
Question details
The user needs to calculate one total invoice amount for each unique customer or phone number when multiple invoices share the same phone number.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an invoice list with customer names, phone numbers, and invoice amounts, and wants a summarized total per unique phone number or customer.
- Observed behavior
- Multiple invoices share the same phone number, so the raw invoice list does not show a single total for each unique customer or phone number.
Make sure your invoice data has separate columns for phone number or customer and invoice amount, with no completely blank header cells or merged cells in the data range.
Use a Dynamic Array Formula with UNIQUE and SUMIF
Fastest way to return a sorted list of unique phone numbers and their invoice totals in one step.
Click an empty cell such as E2 where the spill range has enough space below and to the right.
Type =LET(uMobileNumber,UNIQUE(B2:B100),totals,SUMIF(B2:B100,uMobileNumber,C2:C100),SORT(HSTACK(uMobileNumber,totals))) and press Enter.
Replace B2:B100 with your phone number column and C2:C100 with your invoice amount column to match your data.
Create a PivotTable to Summarize Invoice Amounts by Phone Number
Best for an interactive, refreshable summary without formulas.
Use Remove Duplicates and SUMIF
Use when you need a formula-based total list but your version does not support dynamic array functions.
Total Invoices by Customer or Phone Number in WPS Spreadsheets
WPS Office Spreadsheets gives you Excel-compatible tools to total invoice amounts by unique customer or phone number without extra add-ins. Use dynamic array formulas, PivotTables, or SUMIF and save as .xlsx to share with Microsoft Excel users.
- 1. Open your workbook: Open the invoice file in WPS Office Spreadsheets and confirm the phone number and invoice amount columns are named.
- 2. Enter the summary formula: Select an empty cell and type =LET(uMobileNumber,UNIQUE(B2:B100),totals,SUMIF(B2:B100,uMobileNumber,C2:C100),SORT(HSTACK(uMobileNumber,totals))).
- 3. Press Enter: Press Enter to let WPS spill the unique phone numbers and their invoice totals into the adjacent cells.
- 4. Or insert a PivotTable: Select the data range, click Insert > PivotTable, and drag Phone Number to Rows and Invoice Amount to Values.
- 5. Save as .xlsx: Click File > Save As and choose Excel Workbook (*.xlsx) to keep full Microsoft Office compatibility.
Frequently Asked Questions
Can I sum by customer name instead of phone number?
Yes. Replace the phone number range with the customer name range in the formula, or drag Customer Name to Rows in the PivotTable instead of Phone Number.
Why does my SUMIF formula return 0?
Check whether phone numbers are stored as text in one place and numbers in another, or if invoice amounts are text. Remove extra spaces and use Text to Columns or VALUE to convert the amount column.
Does WPS Office support UNIQUE and HSTACK?
Recent WPS Office versions support dynamic array functions. If your version does not, use the Remove Duplicates and SUMIF method or insert a PivotTable.
How do I keep totals updated when I add more invoices?
Convert the data to a Table with Ctrl+T and reference the table columns, or refresh the PivotTable after adding rows.
Can I save this summary and open it in Microsoft Excel?
Yes. Save the workbook as an Excel Workbook (*.xlsx), and the formula, PivotTable, or SUMIF results can be opened in Microsoft Excel.
