PERCENTOF function

Returns the percentage that one value or range represents of a total, such as category spending as a percent of total budget.

=PERCENTOF(data_subset, data_all)

Generate a PERCENTOF formula

Describe what you need. The generator will reach for PERCENTOF where PERCENTOF is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How PERCENTOF reads its arguments
data_subsetrequireddata_allrequiredPERCENTOF →
ArgumentRequiredDescription
data_subsetRequiredThe component or part value to measure. Can be a single cell, range sum, or calculated expression. If the value is zero, returns 0%.
data_allRequiredThe total or whole value for comparison. Must be non-zero; if empty or zero, returns #DIV/0! error.

Returns

A decimal number between 0 and 1, typically formatted as a percentage (e.g., 0.345 displays as 34.5%).

Availability

Excel: 365 (2024+) · Google Sheets: Not available

Worked examples

1. Find what percentage a single expense category is of monthly total

CategoryMonthBudgetedActual
UtilitiesJanuary150155
GroceriesJanuary400420
EntertainmentJanuary10080
TransportationJanuary300310
Dining OutJanuary200250
=PERCENTOF(420, 1215)

Result: 0.3456 (34.56%)

Groceries actual spending of 420 divided by January's total actual spending of 1,215. This shows that groceries consumed about one-third of the month's budget—useful for identifying which expense categories drive overall spending patterns.

2. Calculate what percentage of annual budget a category received

CategoryMonthBudgetedActual
UtilitiesJanuary150155
UtilitiesFebruary150148
GroceriesJanuary400420
GroceriesFebruary400410
EntertainmentJanuary10080
EntertainmentFebruary10090
TransportationJanuary300310
TransportationFebruary300295
Dining OutJanuary200250
Dining OutFebruary200240
=PERCENTOF(SUMIF(A:A, "Utilities", C:C), SUM(C2:C11))

Result: 0.1304 (13.04%)

Utilities budgeted for the full year (300) divided by total annual budgeted amount across all categories (2,300). Demonstrates how to compare a single category's annual allocation against the entire household budget—essential for multi-period budget analysis.

3. Find what percentage of total overspending came from one category

CategoryMonthBudgetedActualVariance
UtilitiesJanuary1501555
GroceriesJanuary40042020
EntertainmentJanuary10080-20
TransportationJanuary30031010
Dining OutJanuary20025050
UtilitiesFebruary150148-2
GroceriesFebruary40041010
EntertainmentFebruary10090-10
TransportationFebruary300295-5
Dining OutFebruary20024040
=PERCENTOF(SUMIF(A:A, "Dining Out", E:E), SUMPRODUCT(ABS(E2:E11)))

Result: 0.5233 (52.33%)

Dining Out's total variance of 90 compared to the sum of all absolute variances (172). This reveals that more than half of the household's budget deviations originated from a single category—critical for identifying which area drives overspending.

Common errors

Which PERCENTOF error are you seeing?
PERCENTOF returned an error#DIV/0!
Verify that data_all is non-empty and contains numeric values with a sum greater than zero. Wrap with IFERROR to return 0% instead: =IFERROR(PERCENTOF(subset, total), 0%).
#VALUE!
Ensure both arguments reference only numeric cells or use SUMIF to aggregate numeric values from mixed ranges: =PERCENTOF(SUMIF(range, criteria), total).
#N/A
Wrap the lookup formula with IFERROR: =IFERROR(XLOOKUP(lookup_value, array, return_array), 0) to provide a default value before passing to PERCENTOF.
ErrorWhy it happensHow to fix it
#DIV/0!The data_all argument is zero, evaluates to zero, or references an empty range. PERCENTOF cannot divide by zero.Verify that data_all is non-empty and contains numeric values with a sum greater than zero. Wrap with IFERROR to return 0% instead: =IFERROR(PERCENTOF(subset, total), 0%).
#VALUE!Either data_subset or data_all contains non-numeric values (text, logical TRUE/FALSE, or error cells) that cannot be interpreted as numbers.Ensure both arguments reference only numeric cells or use SUMIF to aggregate numeric values from mixed ranges: =PERCENTOF(SUMIF(range, criteria), total).
#N/AIf data_subset or data_all uses VLOOKUP, XLOOKUP, or INDEX/MATCH that fails to find a matching value, the #N/A error propagates into PERCENTOF.Wrap the lookup formula with IFERROR: =IFERROR(XLOOKUP(lookup_value, array, return_array), 0) to provide a default value before passing to PERCENTOF.

Tips and when to use something else

  • Combine PERCENTOF with FILTER to conditionally calculate percentages: =PERCENTOF(SUM(FILTER(amounts, categories="Food")), SUM(amounts)).
  • Use BYROW or BYCOL if you need to apply PERCENTOF row-by-row across a table, rather than manually copying the formula down: =BYROW(data, LAMBDA(row, PERCENTOF(INDEX(row, 1), SUM(data)))).
  • When data_subset includes negative numbers (e.g., refunds or credits), PERCENTOF treats them as negative contributions; wrap with ABS() if you need absolute percentages instead: =ABS(PERCENTOF(variance, total)).
  • Most spreadsheet applications automatically display the decimal result as a percentage when you format the cell as Percentage—avoid manually multiplying by 100 to prevent double-formatting.

Related functions

FILTERReturns filtered rows from an array based on a boolean criteria array, retaining the original order and all columns.SUMIFSUMIF returns the sum of cells in a range that meet a specified criteria, useful for totaling data filtered by a single condition.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.BYROWBYROW applies a LAMBDA to each row of a range and returns a vertical array of the results, enabling row-wise calculations in Excel 365 and Google Sheets.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.

Frequently asked questions

How do I calculate what percentage each row represents of the total in one formula?
Use PERCENTOF with absolute reference for the total: =PERCENTOF(E2, SUM($E$2:$E$11)). The $ symbols lock the total range so it doesn't change when you copy the formula down. Alternatively, combine with BYROW for array-based calculation: =BYROW(E2:E11, LAMBDA(val, PERCENTOF(val, SUM($E$2:$E$11)))).
Does PERCENTOF work with negative numbers?
Yes. If data_subset contains a negative value (such as a -20 variance), PERCENTOF returns a negative percentage. For a variance of -20 against a total of 200, the result is -0.1 or -10%. If you need the absolute percentage regardless of sign, use ABS: =ABS(PERCENTOF(variance, total)).
What's the difference between PERCENTOF and just dividing manually (=subset/all)?
Functionally, they are identical—PERCENTOF simply provides clearer, more concise syntax. The manual formula =subset/all works just as well. PERCENTOF is purely a convenience function that improves readability in complex formulas and requires fewer characters to type.
Can PERCENTOF be used inside conditional formulas?
Yes. Use PERCENTOF as an argument to IF, IFS, and FILTER: =IF(PERCENTOF(actual, budget) > 0.9, "High spending", "Normal"). This allows you to flag categories or rows based on their percentage of a total in a single formula.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17