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
**C****ell G5**where we want the percentage to appear. - Then, write the formula given below in the
**C****ell****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**.

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

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

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

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

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

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

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)`

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.