Quick, clean, and to the point

Summary count with percentage breakdown

Excel formula: Summary count with percentage breakdown
Generic formula 

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:


How this formula works

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.

Dave Bruns

Excel Formula Training

Formulas are the key to getting work done in Excel. In this step-by-step 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. Start building valuable skills with Excel formulas today. Learn more.

I have relied on your website on countless occasions throughout the course of my graduate level finance internship. Compared to a number of unnamed Excel tutorial sites and forums, yours is par-none. It is invariably the only source for consistently accurate and understandable formula functions and syntaxes. I say that as this page has solved an hours-long problem facing me during an all-night work session. Thanks a lot! - Nick
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course