Quick Navigation

PERCENTOF Function

Excel 365, Excel 2021

Summary

The PERCENTOF function calculates what percentage a subset of values represents of a total dataset. It's designed for dynamic array calculations and works seamlessly within GROUPBY and PIVOTBY functions for percentage breakdowns.

Syntax

PERCENTOF(data_subset, data_all)

Parameters

Parameter	Type	Required	Description
data_subset	`Array/Range`	Yes	Subset values to express as percentage of total
data_all	`Array/Range`	Yes	Complete dataset serving as 100% total

Using the PERCENTOF Function

PERCENTOF simplifies percentage calculations in dynamic scenarios. Use it to show contribution percentages within grouped or pivoted data without manual SUM formulas.

Common PERCENTOF Examples

Sales Category Percentage

=PERCENTOF(C3:C4,C3:C14)

Shows what percentage Bib-Shorts and Bike Racks represent of total sales

Monthly Goal Achievement

=PERCENTOF(B2:B4,B$2:B$13)

Calculates each month's sales as percentage of yearly total

Grouped Region Percentage

=PERCENTOF(EastSales,AllRegions)

Within GROUPBY, shows East sales as % of all regions

Frequently Asked Questions

Returns decimal value (0.25). Use NUMBERFORMAT or multiply by 100 and add % symbol for display.

PERCENTOF handles dynamic arrays automatically and is optimized for aggregation functions.

Common Errors and Solutions

#VALUE! error

Cause: Non-numeric values in arrays

Solution: Ensure both ranges contain only numbers

#CALC! error

Cause: Array size mismatch

Solution: Use matching subset within total range

Notes

Available only in Excel 365 and Excel 2021
Logically equivalent to SUM(data_subset)/SUM(data_all)
Ideal for dashboard percentage metrics
Works with spilled array references

Compatibility

Available in: Excel 365, Excel 2021

Not available in: Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 365, Excel 2021