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.
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.
- 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.
Read More: How to Calculate Frequency Using COUNTIF Function in Excel
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.
Read More: How to Use COUNTIF Function with Array Criteria in Excel
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.
Read More: Excel COUNTIF to Count Cells Greater Than 1
Download Practice Workbook
You can download the practice workbook from here.
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.
Related Articles
- Excel COUNTIF Function with Conditional Formatting
- How to Use COUNTIF for Non Contiguous Range in Excel
- COUNTIF Function to Count Cells That Are Not Equal to Zero
- How to Use COUNTIF Function in Excel Greater Than Percentage
- How to Compare Two Columns Using COUNTIF Function
- How to Use Excel COUNTIF Between Time Range
- How to Use COUNTIF to Count Date Less Than Today in Excel
