Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate percent of total without a pivot table in Excel and Google Sheets
Topic:Pivot-style summaries (formula approach)Excel & Google Sheets
=SUMIF(A2:A6,"East",B2:B6)/SUM(B2:B6)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Region | Sales |
| East | 1200 |
| West | 800 |
| East | 600 |
| North | 400 |
| West | 1000 |
=SUMIF(A2:A6,"East",B2:B6)/SUM(B2:B6)→0.45
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Arrange flat data with category labels in A2:A6 and numeric values in B2:B6.
- 2In a summary row, type the category name you want (e.g., 'East') and the formula =SUMIF(A2:A6,"East",B2:B6)/SUM(B2:B6).
- 3Format the result cell as Percentage (Ctrl+Shift+%) to display it as a percent.
- 4Replace the hardcoded "East" with a cell reference pointing to your summary label for a reusable row.
- 5Copy the formula down for each category; the denominator SUM(B2:B6) is the same for all rows — lock it with $B$2:$B$6.
Tips
- Lock the denominator range with absolute references ($B$2:$B$6) so it doesn't shift when you copy the formula down.
- To get 'percent of column total within a group', add a second SUMIF in the denominator filtering to only that group.
- SUMPRODUCT can replace nested SUMIF when you need multi-condition percent of total: =SUMPRODUCT((A2:A6="East")*(B2:B6))/SUM(B2:B6).
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
How do I get percent of row total instead of column total?
Keep the same SUMIF numerator but change the denominator to SUMIF filtered to that row's group rather than SUM of all values.
What if I have two criteria, like Region and Year?
Switch to SUMIFS: =SUMIFS(B2:B6,A2:A6,"East",C2:C6,2024)/SUM(B2:B6).
Will this update automatically when data changes?
Yes — unlike a pivot table, which requires a manual Refresh, the SUMIF/SUM formula recalculates instantly whenever source data changes.
More on Pivot-style summaries (formula approach)
See all →Reference
GETPIVOTDATA
Extract a specific value from a PivotTable using field names and item values as explicit identifiers.
How-toHow to build a pivot-style summary with formulas
Build a formula-driven pivot-style cross-tab in Excel or Google Sheets using SUMIFS with mixed references — live totals by row and column without inserting a pivot table.
How-toHow to build a pivot-style summary with QUERY
Use Google Sheets QUERY with GROUP BY, SUM, and LABEL to build a pivot-style summary table without a manual pivot table — Excel alternative uses SUMIFS.
How-toHow to use GETPIVOTDATA to pull values from a pivot table
Learn how GETPIVOTDATA extracts specific values from a pivot table in Excel and Google Sheets, avoiding broken references when the pivot refreshes.
How-toHow to write formulas for a pivot table calculated field
Understand how pivot calculated fields work and when to replace them with SUMIFS-based formulas in Excel and Google Sheets for more control and flexibility.
How-toHow to group data by month using formulas instead of a pivot
Group and sum transaction data by month using SUMIFS with EOMONTH or TEXT in Excel and Google Sheets — no pivot table needed.