How to Use COUNTIF Function to Calculate Percentage in Excel

Calculating percentages is a very useful mathematical operation while working on an Excel worksheet. We can use the COUNTIF function of Excel for the calculation of the percentage. In this article, I will show the use of the COUNTIF function to calculate percentages with 2 practical examples.

Introduction to COUNTIF Function

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

Syntax:

=COUNTIF(range, criteria)

Arguments:

range: range of cells for matching the criteria.

criteria: criteria is the logic that will determine which cells of the range to be counted.

How to Use COUNTIF Function to Calculate Percentage in Excel: 2 Practical Examples

The COUNTIF function can be used with the COUNTA function to calculate the percentage of both text values and numeric values. In the following sections, I will demonstrate the use of the COUNTIF function to calculate the percentage with 2 practical examples. In the dataset, I have included Subject, Marks and Grade of a student. I will show how to calculate the percentage from there.

1. Apply COUNTIF and COUNTA Functions to Find Percentage of Specific Value in Excel

We can count text values that match criteria with the COUNTIF function and using it with the COUNTA function, we can easily calculate the percentage of a specific text in the data range. Let’s follow the stepwise procedures given below.

Firstly, select Cell G5 where we want the percentage to appear.
Then, write the formula given below in the Cell G5:

=COUNTIF($D$5:$D$12,F5)/COUNTA($D$5:$D$12)

Next, hit Enter.
Afterward, change the format of the cell to Percentage from the Number Format in the Home tab.

Now, use the Fill Handle to copy the formula in the cells below.
Finally, we will see the percentage values in cell range G5:G7.

In the formula, COUNTIF($D$5:$D$12,F5) counts the number of data from fixed range $D$5:$D$12 that match the criteria of Cell F5(the text value “A+”). And we divided the value with COUNTA($D$5:$D$12) which simply counts the number of non-empty cells.

2. Calculate Percentage of Numeric Values Using COUNTIF and COUNTA Functions in Excel

We can calculate the percentage of numeric values that match criteria with the help of the COUNTIF and COUNTA functions. Let’s follow the stepwise procedures given below for that.

Firstly, select cell F5 where we want the percentage to appear.
Secondly, write the following formula in the cell.

=COUNTIF(C5:$C$12,">=80")/COUNTA(C5:$C$12)

Next, press Enter.
Further, change the format of the cell to Percentage from the Home tab.
Consecutively, we will see the percentage result( percentage of marks above 80) in the cell.

Here in the formula, COUNTIF($C$5:$C$12,”>=80″) counts the number of cells that have equal or greater value than 80, dividing it by COUNTA($C$5:$C$12) ( which simply counts the non-empty cells in range C5:$C$12) gives the percentage values.

In a similar manner, we can calculate the percentage of marks above 70 in Cell F6.

Also, we can calculate the percentage of marks above 60 in Cell F7.

How to Determine Percentage Range in Excel

Now, I will show the way to determine the percentage range using the IF function in Excel. Let’s follow the steps given below for the procedures.

Firstly, select Cell E6 where we will calculate the percentage.
Then, write the following formula in the cell.

=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"))))

Afterward, press Enter.
Consequently, we will see the percentage result in the cell.

Now, use the Fill Handle to copy the formula in the cells below.
And we will see the percentage for each cell.

In this formula, the IF function is used.

Here, the first logical test is to check if (D6/120)*100 is equal to 100. If true, it gives an output of 100% and if false, it moves to the second logical test.

Now, the second logical test checks if (D6/120)*100>=80,(D6/120)*100<100. If true, it gives an output of 80%-99% and if false, it moves to the third logical test.

In the third logical test, it checks if (D6/120)*100>=33,(D6/120)*100<80. If true, it gives an output of 33%-80% and if false it moves to the fourth and final logical test.

At last, the formula checks if (D6/120)*100>=0,(D6/120)*100<33). If true, it returns an output F.

How to Use COUNTIFS Function for Percentage Calculation in Excel

The COUNTIFS Function can also be used to determine percentages in Excel. I am showing the procedure with the steps below.

First, select Cell F5 and write the following formula there:

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

Now, hit Enter.

Now, write the following formula in Cell F6.

=COUNTIFS($C$5:$C$12,">=70",$C$5:$C$12,"<80")/COUNTA($C$5:$C$12)

In the following step, hit Enter.

Afterward, select Cell F7 and write the following formula there.

=COUNTIFS($C$5:$C$12,">=60",$C$5:$C$12,"<70")/COUNTA($C$5:$C$12)

Hit Enter.
Finally, we will see the percentage results for each cell.

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

Download Practice Workbook

You can download the practice workbook from here.

Calculate Percentage with COUNTIF Function.xlsx

Conclusion

The percentage calculation is quite an easy task if we use the COUNTIF function. In this article, I have shown 2 practical examples of using the COUNTIF function to calculate the percentage. Hopefully, it will help you. Please leave a comment if you have any queries or suggestions.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

jean Jan 12, 2024 at 4:05 AM

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!
- Reply
  Wasim Akram Jan 16, 2024 at 11:04 AM
  
  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)
  
  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.