Spreadsheet Practice

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

Formula (Excel and Google Sheets)

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

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

Result on the example
62.2% in E1
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

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.

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

Result0.6216216216

  1. B2:B1110 rows × 1 col: $182,000, $126,500, $64,500, $21,000, …

    A range: 10 cells in one column.

  2. {1,2,3}{1 row × 3 cols} 1, 2, 3

    An array of values.

  3. LARGE(B2:B11,{1,2,3}){1 row × 3 cols} 182000, 151500, 126500

    LARGE: k-th largest value.

    • arrayB2:B1110 rows × 1 col: $182,000, $126,500, $64,500, $21,000, …
    • k{1,2,3}{1 row × 3 cols} 1, 2, 3
  4. SUMPRODUCT(LARGE(B2:B11,{1,2,3}))460000

    SUMPRODUCT: Multiplies arrays row by row and adds the results.

    • array1LARGE(B2:B11,{1,2,3}){1 row × 3 cols} 182000, 151500, 126500
  5. SUM(B2:B11)740000

    SUM: Adds numbers and ranges. Skips text and blanks.

    • number1B2:B1110 rows × 1 col: $182,000, $126,500, $64,500, $21,000, …
  6. SUMPRODUCT(LARGE(B2:B11,{1,2,3}))/SUM(B2:B11)0.6216216216

    The / operator divides.

In Google Sheets

The same formula works in Google Sheets: =SUMPRODUCT(LARGE(B2:B11,{1,2,3}))/SUM(B2:B11). We ran it in Google Sheets mode on the same data and it returns 62.2%.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUM(LARGE(B2:B11,SEQUENCE(3)))/SUM(B2:B11)Excel only62.2%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.

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.

Calculate the top 3 share of a total

In E1, calculate the share of total revenue that comes from the three biggest 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).

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.