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

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

  1. North's share of all sales

    ABCDE
    1RegionRepSales
    2NorthAna120067%
    3SouthBen800
    4NorthChloe900
    5WestAna400
    6NorthAna300

    In E2: =PERCENTOF(SUMIF(A2:A6, "North", C2:C6), C2:C6) → 67%

    Try this example

  2. The first two orders' share

    ABCDE
    1RegionRepSales
    2NorthAna120056%
    3SouthBen800
    4NorthChloe900
    5WestAna400
    6NorthAna300

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

    Try this example

Common mistakes

Try PERCENTOF in the playground

Practise it

Related functions