Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • MATCH function: Description, Usage, Syntax, Examples and Explanation
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • Extract all partial matches in Excel
  • Vlookup Examples in Excel
  • Create hyperlink with VLOOKUP in Excel

Data Analysis Examples

  • Conditional Formatting Data bars Examples in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • Get column name from index in Excel Table
  • How to create a Histogram in Excel
  • How to calculate current stock or inventory in Excel

Data Validation Examples

  • Excel Data validation whole percentage only
  • Excel Data validation specific characters only
  • Excel Data validation unique values only
  • Excel Data validation exists in list
  • Data validation must not exist in list

Summary count with percentage breakdown in Excel

by

This tutorial shows Summary count with percentage breakdown in Excel using the example below;

Formula

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

Explanation

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)

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.

Worked Example:   Count cells that do not contain many strings in Excel

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

Worked Example:   Data validation must not exist in list

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.

Post navigation

Previous Post:

Summary count of non-blank categories in Excel

Next Post:

Sum sales in last 30 days by ID in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • How to pad text to match equal length in Excel
  • Remove first character in a cell in Excel
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate nth day of year in Excel
  • Get first day of previous month in Excel
  • Display the current date in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation
  • NETWORKDAYS.INTL function: Description, Usage, Syntax, Examples and Explanation
  • XNPV function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • Calculate loan interest in given year in Excel
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
  • NPER function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles