PERCENTOF function
PERCENTOF returns what share one group makes up of a total, as a decimal. It replaces the usual sum-divided-by-sum pattern and is designed to pair with GROUPBY and PIVOTBY.
Example
Read it as a sentence: find what share the sum of A1:A2 is of the sum of A1:A5, which gives 0.2.
Excel shows 0.2
A
1
1
2
2
3
3
4
4
5
5
Data
Syntax
PERCENTOF(a, b)
-
a(required) - The subset of values (or a single value).
-
b(required) - The whole set of values to compare against.
Returns: The sum of a divided by the sum of b, as a decimal.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=PERCENTOF(1,4) | 0.25 | Scalars |
=PERCENTOF("1","4") | 0.25 | Text number |
=PERCENTOF(Data!A1:A2,Data!A1:A5) | 0.2 | Part of total |
=PERCENTOF(Data!B1:B2,Data!B1:B8) | 0.125 | Mixed range |
=PERCENTOF(1,0) | #DIV/0! | Total 0 |
=PERCENTOF("a",4) | #VALUE! | Text |
=PERCENTOF(1,Data!D1:D2) | #DIV/0! | Blank total |
=PERCENTOF(Data!C1:C5,Data!A1:A5) | #NUM! | Error |
The Data sheet these formulas read
A cell that starts with = holds that formula.
A
B
C
D
1
1
10
1
2
2
20
2
3
3
TRUE
=NA()
4
4
4
5
5
abc
5
6
30
7
FALSE
8
40
Data
Watch out for
- Format the cell as a percent to see 30%.
- Not available in perpetual versions of Excel yet.
Where it works
- Excel
- PERCENTOF is only in Excel for Microsoft 365 and Excel for the web. Microsoft 365 only; not in Excel 2024 or earlier.
- Google Sheets
- Same name, same behaviour for everyday use. Sheets added a PERCENTOF function recently; check its current behaviour.
Part of Math and trig functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .