How to Use the COUNTIF Function to Calculate Percentages in Excel – 2 Examples

The COUNTIF Function

The COUNTIF function counts the number of data that matches a criterion. You can calculate both text values and numeric values.

Syntax:

=COUNTIF(range, criteria)

Arguments:

range: range of cells to match the criteria.

criteria: the logic that determines the cells to be counted.

 


Example 1 – Apply the COUNTIF and the COUNTA Functions to Find the Percentage of a Specific Value in Excel

  • Select G5 and enter the formula below:
=COUNTIF($D$5:$D$12,F5)/COUNTA($D$5:$D$12)
  • Press Enter.
  • Select Number Format in the Home tab.
  • Change the cell format to Percentage.

Excel COUNTIF Percentage

This is the output.

In the formula, COUNTIF($D$5:$D$12,F5) counts the number of data in $D$5:$D$12 matching the criteria in F5 ( “A+”). The value is divided by COUNTA($D$5:$D$12) which counts the number of non-empty cells.

Read More: COUNTIF Between Two Cell Values in Excel


Example 2 – Calculate the Percentage of Numeric Values Using the COUNTIF and the COUNTA Functions in Excel

  • Select F5 and enter the formula below:
=COUNTIF(C5:$C$12,">=80")/COUNTA(C5:$C$12)
  • Press Enter.
  • Select Number Format in the Home tab.
  • Change the cell format to Percentage.

You will see the result in percentage (marks above 80).

Calculate Percentage of Numeric Values Using COUNTIF and COUNTA Functions

In the formula, COUNTIF($C$5:$C$12,”>=80″) counts the number of cells that are equal or greater than 80, dividing the output by COUNTA($C$5:$C$12) (which  counts non-empty cells in C5:$C$12). It returns the value in percentage.
  • Calculate the percentage of marks above 70 in F6:

  • Calculate the percentage of marks above 60 in F7:

Read More: How to Calculate Frequency Using COUNTIF Function in Excel


How to Determine the Percentage Range in Excel

  • Select E6 and enter the formula below:
=IF((D6/120)*100=100,"A",IF(AND((D6/120)*100>=80,(D6/120)*100<100),"80%-100%",IF(AND((D6/120)*100>=33,(D6/120)*100<80),"33%-79%",IF(AND((D6/120)*100>=0,(D6/120)*100<33),"F"))))
  • Press Enter.

This is the output.

How to Determine Percentage Range in Excel

  • Drag down the Fill Handle to see the result in the rest of the cells.

This is the final output.

In the formula:

The first logical test checks if (D6/120)*100 is equal to 100. If true, it returns 100%;  if false, it moves to the second logical test.

The second logical test checks if (D6/120)*100>=80,(D6/120)*100<100. If true, it returns 80%-99%;  if false, it moves to the third logical test.

The third logical test checks if (D6/120)*100>=33,(D6/120)*100<80. If true, it returns 33%-80%;  if false, it moves to the fourth and final logical test.

The formula checks if (D6/120)*100>=0,(D6/120)*100<33). If true, it returns F.

Read More: How to Use COUNTIF Function with Array Criteria in Excel


How to Use the COUNTIFS Function to Calculate Percentage in Excel

  • Select F5 and enter the following formula:

=COUNTIFS($C$5:$C$12,">=80")/COUNTA($C$5:$C$12)
[/wpsm_box]

  • Press Enter.

How to Use COUNTIFS Function for Percentage Calculation in Excel

  • Enter the following formula in CF6.
=COUNTIFS($C$5:$C$12,">=70",$C$5:$C$12,"<80")/COUNTA($C$5:$C$12)
  • Press Enter.

  • Select F7 and use the following formula.
=COUNTIFS($C$5:$C$12,">=60",$C$5:$C$12,"<70")/COUNTA($C$5:$C$12)
  • Press Enter.

This is the output.

Note: the syntax of the COUNTIFS function is similar to the COUNTIF function except for the fact that the COUNTIFS function can take multiple criteria.

Read More: Excel COUNTIF to Count Cells Greater Than 1


Download Practice Workbook

Download the practice workbook.


Related Articles


<< Go Back to Excel COUNTIF Function | Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mehedi Hasan Shimul
Mehedi Hasan Shimul

Md. Mehedi Hasan, with a BSc in Electrical & Electronic Engineering from Bangladesh University of Engineering and Technology, holds a crucial position as an Excel & VBA Content Developer at ExcelDemy. Driven by a deep passion for research and innovation, he actively immerses himself in Excel. In his role, Mehedi not only skillfully addresses complex challenges but also exhibits enthusiasm and expertise in gracefully navigating tough situations, emphasizing his steadfast commitment to consistently deliver exceptional and quality content.... Read Full Bio

2 Comments
  1. Help – not sure where I’m going wrong. I’m trying to calculate the participation % among 21 members of our board. I have the board members going across the top row, the months going down the first column, and I’ve entered the letter “X” for each member that attended.
    Initially, I used this formula: =COUNTIF(B2:V2, “X”)/COUNTA(B2:V2) but the result was 100%, so then I used this formula based on the instructions above: =COUNTIF(B2:V2, A1)/COUNTA($B$2:$V$2), but I get the same result.
    The match comes out to 85.7%, but I can’t see where I’m going wrong. Any help is appreciated!

    • Hello jean,
      Thank you for sharing your problem with us!
      In order to calculate the participation percentage, you can follow the instructions below.

      1. Simply choose a blank cell.
      2. Insert the following formula:
      =COUNTIF($C$5:$C$25, E5)/COUNTA($C$5:$C$25)

      Calculating Percentage of the Participants

      Thus you can calculate the participation percentage.

      Note:
      1. The formula you used did not work because you did not lock the cell range using absolute reference.
      2. Another reason is don’t leave the cells blank for the choosen cell range.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo