Excel functions / Math
PERCENTOF function
What share one set of values is of the whole: SUM(data_subset) ÷ SUM(data_all). Also works as the function in GROUPBY and PIVOTBY.
Syntax
=PERCENTOF(data_subset, data_all)
Arguments
data_subset: The part.data_all: The whole.
When to use it
PERCENTOF gives what share of a total a part is: the sum of the first range divided by the sum of the second. It is written for GROUPBY and PIVOTBY, to show each group as a share of the whole, and works on its own too.
Examples
North's share of all sales
A B C D E 1 Region Rep Sales 2 North Ana 1200 67% 3 South Ben 800 4 North Chloe 900 5 West Ana 400 6 North Ana 300 In E2:
=PERCENTOF(SUMIF(A2:A6, "North", C2:C6), C2:C6)→ 67%The first two orders' share
A B C D E 1 Region Rep Sales 2 North Ana 1200 56% 3 South Ben 800 4 North Chloe 900 5 West Ana 400 6 North Ana 300 In E2:
=PERCENTOF(C2:C3, C2:C6)→ 56%In GROUPBY, PERCENTOF is given by name to show each group's share: =GROUPBY(A2:A6, C2:C6, PERCENTOF).
Common mistakes
- Older versions of Excel. PERCENTOF is in Excel for Microsoft 365 only. Elsewhere, divide by SUM.
Try PERCENTOF in the playground
Practise it
Related functions
- GROUPBY: Groups rows by one or more fields and aggregates values for each group, with a total row: a pivot by formula that updates live. Spills.
- PIVOTBY: Summarises values by row fields down the side and a column field across the top, with totals: a PivotTable by formula that updates live. Spills.
- SUM: Adds up numbers, cells and ranges.
- SUMIF: Adds the cells that meet one condition.