Excel formula: Summary count with percentage breakdown

Generic formula

=COUNTIF(range,criteria)/COUNTA(range)

Summary

To generate a count with a percentage breakdown, you can use the COUNTIF or COUNTIFS function, together with COUNTA.

In the example shown the formula in H4 is:

=COUNTIF(category,F4)/COUNTA(category)

Explanation

COUNTIF is set up to count cells in the named range "category", which refers to B5:B122. The criteria is supplied as a reference to H4, which simply picks up the value in column F.

the COUNTA function simply counts all non-blank cells in the named range category (B5:B122) to generate a total count.

The COUNTIF result is divided by the COUNTA result to generate a percentage. All values in column H are formatted with the "Percentage" number format.

Note: since we already have a count per category in column G, it would be more efficient to pick the that count in this formula instead of recalculating the same count again in column H. However, COUNTIFS and COUNTA are shown together here for the sake of a complete solution.

Author

Dave Bruns

Related formulas

Summary count with COUNTIF

The COUNTIF function takes two arguments: a range of cells to count, and the criteria to use for counting. In this case we want to count by color, so we have set up a small table that lists all colors in column F. This allows us to use the t he...

Summary count by month with COUNTIFS

In this example, we have a list of 100 issues in Columns B to D. Each issue has a date and priority. We are also using the named range "dates" for C5:C104 and "priorities" for D5:D105. Starting in column F, we have a summary table that shows a total...

Get percentage of total

In the example, D6 contains the formula below, copied down: = C6 / $C$11 In cell D6, the result of the calculation is .625. The Percentage number format is applied to cell D6, with 2 decimal places, so Excel displays .625 as 63%. The reference to...

Project complete percentage

At the core, this formula simply divides tasks complete by the total task count: = complete / total which is then formatted as a percentage. To count complete tasks, we count non-blank cells in the range C5:C11: = COUNTA ( C5:C11 ) To count total...

Related functions

Excel COUNTIF Function

COUNTIF is an Excel function to count cells in a range that meet a single condition. COUNTIF can be used to count cells that contain dates, numbers, and text. The criteria used in COUNTIF supports logical...

Excel COUNTA Function

The Excel COUNTA function returns the count of cells that contain numbers, text, logical values, error values, and empty text (""). COUNTA does not count empty cells.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.