# How to Use COUNTIF Function to Calculate Percentage in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.  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.

Read More: COUNTIF Between Two Cell Values in Excel

### 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Mehedi Hasan Shimul

Hi! I am Mehedi Hasan Shimul. As I am an Engineer solving different problems with the help of Excel amuses me. I write Excel related different problem solving articles here. Hope it will help you.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  