Percentage Breakdown in Range Excel and Google Sheets

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 6, 2023
Download Example Workbook

Download the example workbook

This tutorial demonstrates how to use the COUNTIF and COUNTA functions to calculate percentage breakdown for a range.

Percentage Breakdown in Range main Function

Percentage Breakdown of Values in a Range

To create a summary breakdown containing the percentage of times values appear in a range, use this formula:

=COUNTIF(Range, Criteria) / COUNTA(Range)

Let’s look at an example, in this data set, we have a list of job applications for the role of a Financial Analyst. In this case, we want to count the percentage of applications for each study background.

The COUNTIF function counts the number of times each item appears in the list

=COUNTIF($C$2:$C$14, $E2)

PIC 01

While the COUNTA function will count all the cells in that range (skipping blank cells).



By dividing the COUNTIF function by the COUNTA function, you calculate a percentage representing how often a certain item appears in the list.

excel percentage breakdown example

=COUNTIF($C$2:$C$31, Study Background) / COUNTA($C$2:$C$31)

By organizing the study backgrounds in a list, we can use a relative reference to drag the formula down to build a summary table.

excel percentage breakdown gif

After doing that, change the format of the values to a percentage value.

excel percentage breakdown arrow percentage

We now have a summary count with a percentage breakdown for each study background in the job applications.


Percentage Breakdown in Range in Google Sheets

excel percenttage breakdown google sheets

We use the same formula structure for Google Sheets to get the same result.

=COUNTIF(Range, Criteria) / COUNTA(Range)

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List