# How to calculate each category's share of the total in Excel

Sum the category with SUMIF and divide by the grand total: `=SUMIF($A$2:$A$11,E2,$B$2:$B$11)/SUM($B$2:$B$11)`.

**Excel:** `=SUMIF($A$2:$A$11,E2,$B$2:$B$11)/SUM($B$2:$B$11)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Category | Amount |  |  | Category | Share |
| Housing | 1800 |  |  | Housing |  |
| Food | 120.5 |  |  | Food |  |
| Transport | 60 |  |  | Transport |  |
| Food | 85.25 |  |  |  |  |
| Housing | 240 |  |  |  |  |
| Transport | 45.75 |  |  |  |  |
| Food | 210.4 |  |  |  |  |
| Transport | 32 |  |  |  |  |
| Food | 64.85 |  |  |  |  |
| Housing | 95 |  |  |  |  |

Formula in F2, filled to F4: `=SUMIF($A$2:$A$11,E2,$B$2:$B$11)/SUM($B$2:$B$11)`

Result: 77.5%; 17.5%; 5.0%

## How it works

SUMIF adds up the amounts for the category next to the formula: Housing comes to 2,135. Dividing by the total of every amount, 2,753.75, gives its share: 77.5%. The data ranges are locked with $, so only E2 changes as you fill down, and the three shares add up to 100%.

## Other ways

- `=SUMPRODUCT(($A$2:$A$11=E2)*$B$2:$B$11)/SUM($B$2:$B$11)`: The same share with SUMPRODUCT, which can take extra conditions such as a month test.

## Common mistakes

- Without $ on the ranges, they shift as you fill down and later categories miss rows.
- A category spelled two ways ("Food" and "Groceries") splits its share. Clean the labels, or the shares won't mean much.

## Questions

**How do I list the categories automatically?**

In Excel 365 or Google Sheets, put `=UNIQUE(A2:A11)` in E2 and fill the share formula beside it.

**Can a pivot table show this?**

Yes: put Category in Rows and Amount in Values, then Show Values As, % of Grand Total.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/percentage-of-total-by-category