How to Calculate Loyalty Points in WPS Excel from Customer Purchases
Question details
The user needs to calculate loyalty points for customers who make multiple purchases, either by counting each purchase or awarding one point per currency unit spent using a running customer total.

- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- A user tracks customer purchases in a spreadsheet and wants to create formulas that automatically calculate loyalty points based on purchase frequency or spending.
- Observed behavior
- The goal is to produce a running loyalty-point total per customer that updates as new purchase rows are added.
Open the workbook where customer names are in one column and purchase amounts are in another, and confirm the data starts at row 2 with column headers in row 1.
Calculate Running Loyalty Points with COUNTIF and SUMIF
Use this approach when you need a row-by-row loyalty point calculation that automatically accumulates each customer's purchases.
The COUNTIF formula creates a running purchase count for each customer by locking the top row of the range and letting the bottom row expand as you fill down.
For spending-based points, SUMIF creates a running total for the customer and INT converts the result to whole points based on the spending amount required.
In row 2 of an empty column, enter =COUNTIF($A$2:A2,A2) if customer names are in column A, then press Enter.
In another empty column, enter =INT(SUMIF($A$2:A2,A2,$B$2:B2)/1) if purchase amounts are in column B, and replace 1 with the currency amount required for one point.
Select both formula cells in row 2, then drag the fill handle down through all customer rows so each row recalculates the running count and loyalty points.
Create Loyalty Point Formulas in WPS Office Excel
Use WPS Office Excel to calculate loyalty points with familiar COUNTIF and SUMIF formulas while working in a free, lightweight, Microsoft Excel-compatible environment.
- 1. Open WPS Office Excel: Launch WPS Office, open the workbook containing customer names and purchase amounts, and select the cell where the first loyalty-point formula will go.
- 2. Enter the COUNTIF formula: In row 2 of the selected column, type =COUNTIF($A$2:A2,A2) to count purchases for the customer in column A.
- 3. Enter the spending-based points formula: In the next column, type =INT(SUMIF($A$2:A2,A2,$B$2:B2)/1), and replace 1 with the spending amount required to earn one point.
- 4. Copy the formulas down: Select both formula cells, use the fill handle to drag the formulas down through the remaining customer rows, and verify each result.

Frequently Asked Questions
How do I count purchases for each customer in WPS Excel?
Use =COUNTIF($A$2:A2,A2) in row 2 and fill the formula down. The expanding range returns a running purchase count for the customer in column A.
How do I calculate one point per currency unit spent?
Use =INT(SUMIF($A$2:A2,A2,$B$2:B2)/1) in row 2 and replace 1 with the amount that should equal one point.
Can I change the spending amount required for one loyalty point?
Yes, change the divisor in the SUMIF formula. For example, use /10 to award one point for every 10 currency units spent.
Will these formulas work if I open the file in Microsoft Excel?
Yes, WPS Office Excel uses the same COUNTIF, SUMIF, and INT functions and saves .xlsx files that Microsoft Excel can open.
What should I do if I add new customer rows?
Fill the formula down into the new rows, or convert the range to an Excel Table so the formulas extend automatically.




