Function Problems

How to Calculate Loyalty Points in WPS Excel from Customer Purchases

Steve KSteve K Nov 13, 2026 869 views

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.

How to Calculate Loyalty Points in WPS Excel from Customer Purchases
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.
Before you start

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.

Solution 1Recommended

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.

1
Enter the purchase-count formula

In row 2 of an empty column, enter =COUNTIF($A$2:A2,A2) if customer names are in column A, then press Enter.

2
Enter the spending-based points formula

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.

3
Fill the formulas down

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.

Result: You can now see each customer's running purchase count and spending-based loyalty points in each row of the worksheet.
WPS Office Excel formula

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. 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. 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. 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. 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.
Uses the same COUNTIF, SUMIF, and INT formulas as Microsoft ExcelOpens and saves .xlsx, .xls, and .csv files without reformattingFree download with a familiar ribbon interface and no subscription requiredHandles customer purchase lists with fast performance
microsoft office alternative - wps office

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.