# How to sum the top 3 values with a condition in Excel

Filter first, then take the three largest: `=SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}))`.

**Excel:** `=SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}))` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** `=SUM(ARRAY_CONSTRAIN(SORT(FILTER(C2:C10,B2:B10=F1),1,FALSE),3,1))`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Rep | Region | Sales |  | Region | East |
| Ana | East | 48200 |  | Top 3 total |  |
| Ben | West | 61750 |  |  |  |
| Cara | East | 57300 |  |  |  |
| Dev | East | 39800 |  |  |  |
| Eli | West | 66400 |  |  |  |
| Fay | East | 44150 |  |  |  |
| Gus | East | 59900 |  |  |  |
| Hana | West | 52600 |  |  |  |
| Ivo | East | 41300 |  |  |  |

Formula in F2: `=SUM(LARGE(FILTER(C2:C10,B2:B10=F1),{1,2,3}))`

Result: $165,400

## 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.

## Other ways

- `=SUMPRODUCT(LARGE((B2:B10=F1)*C2:C10,{1,2,3}))`: 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-top-3-values-with-a-condition