How to sum the top 3 values with a condition in Excel
Excel formula
=SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}))Filter first, then take the three largest: =SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3})).
- Result on the example
- $165,400 in
F2 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Different formula, see below
See it work
The formula is already in F2. Change any input and the result updates, or edit the formula to experiment.
How it works
FILTER keeps only the East sales. LARGE with {1,2,3} returns the 1st, 2nd and 3rd largest of those, and SUM adds them: 59,900 + 57,300 + 48,200. West's bigger numbers never enter the calculation. The Google Sheets version sorts the filtered sales from largest down and keeps the first three.
=SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}))Result165400
C2:C109 rows × 1 col: $48,200, $61,750, $57,300, $39,800, …A range: 9 cells in one column.
B2:B109 rows × 1 col: "East", "West", "East", "East", …A range: 9 cells in one column.
F1"East"The value in F1.
B2:B10=F1{9 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …The = operator tests whether the two sides are equal.
FILTER(C2:C10,B2:B10=F1){6 rows × 1 col} 48200, 57300, 39800, 44150, …FILTER: Rows of a range that meet every condition. #N/A when none match.- range
C2:C109 rows × 1 col: $48,200, $61,750, $57,300, $39,800, … - condition1
B2:B10=F1{9 rows × 1 col} TRUE, FALSE, TRUE, TRUE, …
- range
{1,2,3}{1 row × 3 cols} 1, 2, 3An array of values.
LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}){1 row × 3 cols} 59900, 57300, 48200LARGE: k-th largest value.- array
FILTER(C2:C10,B2:B10=F1){6 rows × 1 col} 48200, 57300, 39800, 44150, … - k
{1,2,3}{1 row × 3 cols} 1, 2, 3
- array
SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}))165400SUM: Adds numbers and ranges. Skips text and blanks.- number1
LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}){1 row × 3 cols} 59900, 57300, 48200
- number1
In Google Sheets
Google Sheets needs a different formula: =SUM(ARRAY_CONSTRAIN(SORT(FILTER(C2:C10,B2:B10=F1),1,FALSE),3,1)). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUMPRODUCT(LARGE((B2:B10=F1)*C2:C10,{1,2,3})) | $165,400 | Every version: other regions become 0, so the largest values are East's, as long as the region has at least three positive sales. |
Common mistakes
- If the region has fewer than three sales, LARGE returns #NUM!. Use
SEQUENCE(MIN(3,COUNTIF(B2:B10,F1)))for the ranks. =SUM(LARGE(C2:C10,{1,2,3}))ignores the region: it adds the top three overall.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum the top 3 values with a condition
F2, add up the three highest sales in the region in F1.Questions
How do I make the number of values a cell input?
Use SEQUENCE for the ranks: =SUM(LARGE(FILTER(C2:C10,B2:B10=F1),SEQUENCE(G1))).
How do I average the top 3 instead?
Swap SUM for AVERAGE.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.