How to Total Invoices by Name and Highlight Totals Above $500 in Excel
Question details
The user needs to calculate invoice totals grouped by customer name, return the total only on the last matching row, and highlight customers whose invoice total exceeds $500.

- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a worksheet with customer names and invoice amounts and wants a clean summary that flags high-value customers.
- Observed behavior
- The desired result is a helper column that shows each customer’s total on the final occurrence and a conditional formatting rule that highlights rows where the customer total is above $500.
Confirm that your invoice data is arranged with customer names in one column and invoice amounts in another numeric column, and identify the exact data range you want to format, for example A2:D23.
Total invoices by name and highlight values above $500 with COUNTIF, SUMIF, and conditional formatting
Use this method in WPS Spreadsheets or any Excel version when you want a straightforward helper-column approach that displays each customer total only on the last matching row.
This approach uses COUNTIF to identify the last occurrence of each name and SUMIF to calculate the customer total, then adds a conditional formatting formula to flag totals over $500.
Select cell D2 and enter =IF(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$23,A2),SUMIF($A$2:A2,A2,$C$2:C2),"") to return the total only on the last occurrence of each name.
Drag the fill handle from D2 down through the last row of your invoice table so each customer’s final row shows the invoice total.
Select A2:D23, click Home > Conditional Formatting > New Rule, choose Use a formula to determine which cells to format, and enter =SUMIF($A$2:$A$23,$A2,$C$2:$C$23)>500.
Click Format, choose a fill or font color, and click OK to highlight all rows where the customer invoice total is above $500.

Use a dynamic array formula in Microsoft 365 with LET, XMATCH, and MAP
Use this method in Microsoft 365 when you prefer a single spill formula without filling a helper column down manually.
Calculate invoice totals by name and highlight values over $500 in WPS Spreadsheets
WPS Spreadsheets can directly handle this invoice-totaling task with familiar SUMIF and COUNTIF formulas plus conditional formatting, so you do not need another Office tool.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, click Menu > Open, and select the Excel workbook that contains the invoice list.
- 2. Add the helper-column formula: In cell D2, enter =IF(COUNTIF($A$2:A2,A2)=COUNTIF($A$2:$A$23,A2),SUMIF($A$2:A2,A2,$C$2:C2),"") and fill it down through the last row.
- 3. Apply conditional formatting: Select A2:D23, click Home > Conditional Formatting > New Rule, choose Use a formula, and enter =SUMIF($A$2:$A$23,$A2,$C$2:$C$23)>500.
- 4. Choose the highlight style: Click Format, pick a fill or font color, and click OK to highlight invoice rows where the customer total exceeds $500.
- 5. Save the workbook: Press Ctrl+S or click Menu > Save As to keep the workbook in Excel-compatible .xlsx format.

Frequently Asked Questions
Why does my SUMIF formula return 0 instead of the invoice total?
Check whether the invoice amounts are stored as text. In WPS Spreadsheets, select the amount column, right-click, choose Format Cells, set the category to Number, and ensure the SUMIF range matches the actual data range.
Can I show the customer total on every row instead of only the last matching row?
Yes. In cell D2 enter =SUMIF($A$2:$A$23,A2,$C$2:$C$23) and fill down. This repeats the same customer total on every matching row; the conditional formatting formula can remain =SUMIF($A$2:$A$23,$A2,$C$2:$C$23)>500.
Will these formulas work in WPS Office Spreadsheets?
Yes. COUNTIF, SUMIF, and conditional formatting formula rules are supported in WPS Spreadsheets, and the workbook remains compatible with Excel .xlsx files.
How do I highlight only the total cell instead of the whole row?
Select only the helper-column range, such as D2:D23, create a new conditional formatting rule, and use either =SUMIF($A$2:$A$23,$A2,$C$2:$C$23)>500 or =$D2>500 to format the total cell.




