# How to calculate the percentage within each group using COUNTIFS in Excel

Count the group's rows that meet the condition and divide by all the group's rows: `=COUNTIFS($B$2:$B$13,E2,$C$2:$C$13,"Remote")/COUNTIF($B$2:$B$13,E2)`.

**Excel:** `=COUNTIFS($B$2:$B$13,E2,$C$2:$C$13,"Remote")/COUNTIF($B$2:$B$13,E2)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F |
| --- | --- | --- | --- | --- | --- |
| Employee | Department | Work mode |  | Department | % remote |
| E-01 | Sales | Remote |  | Sales |  |
| E-02 | Sales | Office |  | IT |  |
| E-03 | IT | Remote |  | HR |  |
| E-04 | IT | Remote |  |  |  |
| E-05 | HR | Office |  |  |  |
| E-06 | Sales | Hybrid |  |  |  |
| E-07 | IT | Office |  |  |  |
| E-08 | HR | Remote |  |  |  |
| E-09 | Sales | Remote |  |  |  |
| E-10 | IT | Remote |  |  |  |
| E-11 | HR | Office |  |  |  |
| E-12 | Sales | Office |  |  |  |

Formula in F2, filled to F4: `=COUNTIFS($B$2:$B$13,E2,$C$2:$C$13,"Remote")/COUNTIF($B$2:$B$13,E2)`

Result: 40.0%; 75.0%; 33.3%

## How it works

The top of the fraction counts people in the department who work remotely: COUNTIFS with two conditions. The bottom counts everyone in the department with COUNTIF. Sales has 5 people and 2 work remotely: 40%. Each department is compared with its own size, not with the whole company.

## Other ways

- `=COUNTIFS($B$2:$B$13,E2,$C$2:$C$13,"Remote")/COUNTA($B$2:$B$13)`: Divides by the whole company: the department's remote workers as a share of all employees. (Gives Sales 16.7% (2 of 12).)

## Common mistakes

- Dividing by the company total answers a different question: what share of all staff are remote workers in that department.
- A department with no rows makes COUNTIF return 0 and the formula #DIV/0!. Wrap it in IFERROR.

## Questions

**How do I put the condition in a cell?**

Reference it: `=COUNTIFS($B$2:$B$13,E2,$C$2:$C$13,$G$1)/COUNTIF($B$2:$B$13,E2)`, with "Remote" in G1.

**Can a pivot table show this?**

Yes: Department in Rows, Work mode in Columns, a count in Values, then Show Values As, % of Row 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-within-each-group