PERCENTOF Function in Microsoft Excel: Syntax, Examples & Troubleshooting

=PERCENTOF(data_subset, data_all)

Both arguments are required. data_subset supplies the values whose contribution you want; data_all supplies the entire comparison set. A range is convenient, but the calculation is about sums, not the number of cells. It does not establish that the first range really belongs to the second, or remove values from the denominator automatically.

Use the same measurement units in both arguments. Comparing pounds of one product with dollars for all products produces a numerical ratio without a coherent business meaning. Keep both ranges on the same reporting period and inclusion policy.

Enter this table in A1:B5:

1. Enter =PERCENTOF(B2,B2:B5) in D2.
2. The expected numeric result is 0.15, because 120 divided by 800 equals 0.15.
3. Apply Percentage format to D2 to display 15%.
4. In D3, enter =PERCENTOF(B2:B3,B2:B5). Its expected result is 0.5, or 50%.

The second result combines the first two products: (120+280)/800. It is not the average of their revenues and not a cell-count percentage.

For the same table, one of four products represents 25% of product rows, but bike racks represent 15% of revenue. The formula deliberately weights each item by the value being summed. If the question concerns record counts, count eligible records first instead of handing revenue ranges to PERCENTOF.

The explicit arithmetic checks are =SUM(B2)/SUM(B2:B5) and =SUM(B2:B3)/SUM(B2:B5). They have expected results 0.15 and 0.5. These are also clear alternatives in an Excel version that does not recognize PERCENTOF.

Enter =PERCENTOF(B2,$B$2:$B$5) in C2 and fill through C5. Expected results, in order, are 15%, 35%, 25%, 25%. The dollar signs hold the denominator on the full reporting set while the numerator advances one row at a time. =SUM(C2:C5) is expected to return 1, displayed as 100%.

Without the absolute denominator, a copied formula can slide from B2:B5 to B3:B6. A plausible percentage would then compare each product to a different total. Check the final row’s formula, not just the first row’s output.

PERCENTOF is also documented for use with GROUPBY and PIVOTBY. In those contexts the supplied subset and whole set depend on the aggregation’s configuration. A standalone range calculation, as above, is a useful control before building a grouped report; it does not certify the grouping configuration automatically.

• A displayed 0.15 rather than 15%: apply Percentage format; do not multiply by 100 and then apply Percentage format as well.
• Zero total: the documented sum-divided-by-sum model has a zero denominator. Treat that share as undefined; do not silently replace it with 0% without a reporting policy.
• Above 100%: the numerator may include extra records, overlap, or amounts not present in the denominator. For example, a subset sum of 900 against total 800 gives 112.5% mathematically.
• Negative values: refunds or corrections can make the total smaller or negative. A percentage may be mathematically valid while needing explanation; define whether the report is gross or net.
• Numbers stored as text: the SUM-based meaning means range text can undermine the totals. Inspect stored values rather than relying on their appearance.
• #NAME?: verify that your Excel build supports this newer function. Microsoft’s page currently lists Microsoft 365; it does not claim all perpetual releases.

Microsoft’s reference checked on October 17, 2026 lists: Excel for Microsoft 365. Microsoft’s alphabetical catalog, checked October 17, 2026, identifies PERCENTOF with a Microsoft 365 introduction marker. That marker does not establish an exact rollout date, update channel or perpetual-license release. The last functioning version is unverified; no retirement is asserted.

The reviewed PERCENTOF page lists only Excel for Microsoft 365. Its generic product label is not proof of every update channel, operating system, or older perpetual release. Confirm availability in the intended build.

Example verification: Expected numerical results are independently calculated; text and shape expectations follow the documented contract. These examples have not been tested in Microsoft Excel. No screenshot or native execution is claimed.

SUM in Excel | SUMIF in Excel | SUMIFS in Excel

• Microsoft PERCENTOF reference, retrieved October 17, 2026.