Practice free

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.

Practice this in the app

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.

FormulaExcel showsCase
=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.

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 .