The Problem
A customer with one big order is easy to notice. A customer with several medium orders can be easier to miss, even if their combined value is higher. That is why a simple row-by-row scan is not enough for loyalty segmentation.
This challenge focuses on total customer value, not single-order value. We need to add each customer's orders together first, then use that total to decide whether each row should be labeled VIP or STANDARD.
How We Solve It
The key formula here is SUMIF. It looks through the name column, finds every row that belongs to the current customer, and adds those order amounts together. Once we have that total, IF turns it into the final category label.
Method 1: SUMIF plus IF
Method 1: Add each customer total before deciding whether they qualify as VIP.
This is the formula pattern the challenge expects. For each row, SUMIF calculates that customer's total spend across the list. If the result is greater than 5000, the row becomes VIP. Otherwise it stays STANDARD.
This solves the challenge because the label depends on total customer value, not on the amount from one row alone. The formula gathers every matching order for that name first, then makes the VIP decision from the combined spend.
=IF(SUMIF($A$2:$A$6, A2, $B$2:$B$6) > 5000, "VIP", "STANDARD")
Method 2: Map totals to more tiers
Method 2: Map spending totals to loyalty tiers when you need more than two categories.
If the business later adds Bronze, Silver, and Gold levels, a lookup table becomes a good next step. The total spend is still the starting point, but then a lookup formula maps that number to a label instead of using just one threshold.
This solves a broader version of the same problem. Instead of stopping at VIP versus standard, it lets the worksheet translate total spend into multiple category levels when the segmentation model becomes more detailed.
=VLOOKUP(SUMIF($A$2:$A$6, A2, $B$2:$B$6), $E$2:$F$5, 2, TRUE)
Method 3: Filter the VIP list
Method 3: Pull only the high spenders into a separate view.
Once the categories are built, a filtered VIP view becomes easy. This is not part of the validator, but it is a practical next move when the team wants a separate outreach list instead of a full mixed table.
This solves the follow-up workflow problem. After the labels are created, the formula can pull just the VIP rows into their own list, which is useful when the marketing team wants a ready-to-use audience instead of the full sales table.
=FILTER(A2:C6, C2:C6="VIP")
Function Explanation
1. SUMIF
SUMIF adds values that match one condition. In this challenge the condition is the customer name, so it lets us calculate lifetime spend from repeated rows.
Learn more this functionSUMIF
2. IF
IF turns the numeric total into a category label. It keeps the output readable and makes the final count and revenue summary much easier to build.
3. COUNTIF
COUNTIF is used in the summary to count how many rows are labeled VIP. That gives us the final row count without another manual pass through the sheet.
The most important idea here is that customer value is being measured across all of their orders. If you skip that step and judge each row alone, you get the wrong answer.
Calculate each customer's combined spend, label each row as VIP or STANDARD, and then finish the summary so the sheet shows both the VIP row count and the revenue tied to those VIP rows.