# How to calculate the top 3 share of a total in Excel

Add the three largest values with LARGE and divide by the total: `=SUMPRODUCT(LARGE(B2:B11,{1,2,3}))/SUM(B2:B11)`.

**Excel:** `=SUMPRODUCT(LARGE(B2:B11,{1,2,3}))/SUM(B2:B11)` (Excel 2021, 2024 and Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Customer | Revenue |  | Top 3 share |
| Northwind | 182000 |  |  |
| Contoso | 126500 |  |  |
| Fabrikam | 64500 |  |  |
| Litware | 21000 |  |  |
| Adatum | 96000 |  |  |
| Proseware | 45500 |  |  |
| Wingtip | 12000 |  |  |
| Tailspin | 33000 |  |  |
| Woodgrove | 8000 |  |  |
| Alpine | 151500 |  |  |

Formula in E1: `=SUMPRODUCT(LARGE(B2:B11,{1,2,3}))/SUM(B2:B11)`

Result: 62.2%

## How it works

`LARGE(B2:B11,{1,2,3})` returns the largest, second-largest and third-largest revenues, and SUMPRODUCT adds them: 460,000. Divided by the total of 740,000, the top three customers bring in 62.2%, a concentration figure investors and lenders often ask for.

## Other ways

- `=SUM(LARGE(B2:B11,SEQUENCE(3)))/SUM(B2:B11)`: Excel 2021 and Microsoft 365: SEQUENCE builds the list 1, 2, 3, so the 3 can come from a cell.

## Common mistakes

- Duplicates in the data are fine: LARGE counts tied values separately, so two equal revenues both count.
- If a customer appears on several rows, total them per customer first (SUMIF or a pivot table); otherwise the "top 3" are rows, not customers.

## Questions

**How do I make the number of top items a cell?**

In Excel 365 use SEQUENCE with the cell: `=SUM(LARGE(B2:B11,SEQUENCE(E2)))/SUM(B2:B11)`.

**How do I get the share of the top 10%?**

Sum the values above the 90th percentile: `=SUMIF(B2:B11,">="&PERCENTILE.INC(B2:B11,0.9))/SUM(B2:B11)`.

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/share-of-top-n