Return to Excel Formulas List

Percentage Breakdown in Range Excel and Google Sheets

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:

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

PIC 01

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

CountA

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

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.

Combine

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.