If you want to calculate frequency in Excel, using the **COUNTIF function** is one of the smartest ways to do so. Calculating frequency means counting the number of occurrences of a particular value within a specified range of values. There are **2 **types of data. These are-

*Quantitative Data (Numbers)**Qualitative Data (Texts)*

In this article, we will learn how to calculate the frequency for both data types using the **COUNTIF **function in Excel. So, let’s start this article and explore these simple methods.

**Table of Contents**hide

## Download Practice Workbook

## 2 Simple Methods to Calculate Frequency Using COUNTIF Function in Excel

In this section of the article, we will learn **2 **simple methods to **calculate frequency using the COUNTIF function in Excel**.

Not to mention that we have used *Microsoft Excel 365 *version for this article, you can use any other version according to your convenience.

### 1. Using COUNTIF with SUM Functions

Using the combination of the **COUNTIF **and the **SUM function** provides an efficient solution to calculate frequency in Excel. Suppose we have the **Math Marks of Grade 8 Students** of a school as our dataset. In the dataset, we have the **Range **of the **Numbers **specified. Our goal is to find the frequency of the **“Marks” **of the students within these **Ranges**. Now, let’s follow the steps mentioned below to do this.

**Steps:**

- Firstly, use the following formula in cell
**D17**.

`=COUNTIF($C$5:$C$14,"<="&B17)`

Here, the range of cells **$C$5:$C$14 **indicates the array of the Marks obtained by the students, and cell **B17 **refers to the **1st **cell of the **Number **column.

- After that, press
**ENTER**.

As a result, you will have the frequency of *Marks*, which is less than or equal to **70 **in cell **D17**.

- Following that, enter the formula given below in cell
**D18**.

`=COUNTIF($C$5:$C$14,"<="&B18)-SUM($D$17:D17)`

Here, cell **B18** indicates the **2nd **cell of the *Number *column, and range** $D$17:D17** represents the cells of the **Frequency **column.

**Formula Breakdown**

**COUNTIF($C$5:$C$14,”<=”&B18)**- Here,
**$C$5:$C$14**→ It is theargument.*range* **“<=”&B18**→ This refers to theargument,*criteria***Output**→**6**.

- Here,
**SUM($D$17:D17)**→ Returns the sum of the range**$D$17:D17**.**Output**→**3**.

**COUNTIF($C$5:$C$14,”<=”&B18)-SUM($D$17:D17) → 6-3**.**Output → 3**.

- Then hit
**ENTER**.

Consequently, you will have the frequency of *Marks *that are greater than **70 **and less than or equal to **80 **in cell **D18**, as shown in the following image.

- Finally, use the
**AutoFill**option of Excel to get the rest of the outputs as demonstrated in the following picture.

**Read More: ****COUNTIF Excel Example (22 Examples)**

### 2. Calculating Frequency for Texts

Applying the **COUNTIF **function, we can calculate the frequency of not only the numbers but also the **texts**. Let’s say, we have the following dataset to demonstrate the application of the **COUNTIF **function to calculate the frequency of texts. Now, use the instructions outlined below to do this.

**Steps:**

- Firstly, apply the formula given below in cell
**C17**.

`=COUNTIF(C5:C14,"Passed")`

Here, the range of cells **C5:C14** refers to the cells of the **Status **column.

- Then, press
**ENTER**.

As a result, you will have the number of students that get **“Passed” **status in the exam (see cell **C17**).

- After that, enter the following formula in cell
**C18**.

`=COUNTIF(C5:C14,"Failed")`

- Now, hit
**ENTER**.

Consequently, you will have the following output on your worksheet, as shown in the image below.

**Read More: ****How to Count If Cells Contains Text from List in Excel**

**Similar Readings**

**How to Use COUNTIF to Count Date Less Than Today in Excel****COUNTIF vs COUNTIFS in Excel (4 Examples)****How to Use COUNTIF with SUBTOTAL in Excel (2 Methods)****Count Blank Cells with Excel COUNTIF Function: 2 Examples****Use Excel COUNTIF Function to Count Cells Greater Than 0**

## How to Count Frequency Using COUNTIFS Function in Excel

In this section of the article, we will learn to count frequency by using the **COUNTIFS function** in Excel. Let’s follow the steps mentioned below to do this.

**Steps:**

- Firstly, use the following formula in cell
**C17**.

`=COUNTIF($C$5:$C$14,"<="&B17)`

- Then, press
**ENTER**.

Subsequently, you will see the following output on your worksheet.

- After that, apply the formula given below in cell
**C18**.

`=COUNTIFS($C$5:$C$14,">"&B17,$C$5:$C$14,"<="&B18)`

- Now, hit
**ENTER**.

As a result, you will have the frequency of *Marks *that are greater than **70 **but less than or equal to **80 **in cell **C18**.

- Finally, use Excel’s
**AutoFill**feature to get the rest of the frequencies as demonstrated in the following image.

**Read More: ****Excel COUNTIF with Greater Than and Less Than Criteria**

## How to Find Frequency of a Range in Excel

In Excel, we often need to find the frequency of a particular value within a range. We can do this simply by using the **FREQUENCY function** in Excel. Let’s follow the procedure discussed in the following section to do this.

**Steps:**

- Firstly, use the following formula in cell
**D17**.

`=FREQUENCY(C5:C14,B17:B20)`

Here, the range of cells **C5:C14** refers to the cells of the **Marks **column, and** B17:B20** indicates the cells of the **Bins **column.

- After that, press
**ENTER**.

That’s it! The **FREQUENCY **function will return the following outputs as shown in the following picture.

You can also **follow any of the methods discussed in this article** to find the frequency of a value within a range in Excel.

**Read More: ****Apply COUNTIF Function in Multiple Ranges for Same Criteria**

## How to Count Frequency of Values in Multiple Columns in Excel

In the methods that have been discussed in the previous sections, we have searched for the number of occurrences of a particular value in a single column. Now, we will count the frequency of values in **multiple columns** in Excel. Let’s follow the steps mentioned below.

**Steps:**

- Firstly, use the formula below in cell
**D17**.

`=COUNTIFS($D$5:$D$14,$B$17,$C$5:$C$14,C17)`

Here, the range of cells **$D$5:$D$14** represents the cells of the **Section **column, cell** $B$17 **indicates the selected **Section**, and cell **C17 **represents the selected **Status**.

- Following that, hit
**ENTER**.

Subsequently, you will have the frequency of the students of **“Section A” **that get **“Passed” **status in the exam.

- Now, drag the
**Fill Handle**to copy down the formula up to cell**D18**.

As a result, you will know the number of the students of **“Section A” ** who gather **“Failed” **status in the exam.

- After that, enter the following formula in cell
**D19**.

`=COUNTIFS($D$5:$D$14,$B$19,$C$5:$C$14,C17)`

- Then, press
**ENTER**.

At this point, you will have the frequency of the students of **Section B **that get **“Passed” **status in the exam in cell **D19**.

- Finally, drag the
**Fill Handle**up to cell**D20**to copy down the formula.

Consequently, you will get the number of the students of **Section B ** that get **“Failed” **status in the exam (in cell **D20**), as demonstrated in the following picture.

**Read More: ****How to Compare Two Columns Using COUNTIF Function (4 Ways)**

## Practice Section

In the **Excel Workbook**, we have provided a **Practice Section **on the right side of the worksheet. Please practice it by yourself.

## Conclusion

So, these are the most common & effective methods you can use anytime while working with your Excel datasheet to **calculate frequency using the COUNTIF function in Excel**. If you have any questions, suggestions, or feedback related to this article you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, **ExcelDemy**.

## Related Articles

**Excel COUNTIF to Count Cell That Contains Text from Another Cell****How to Use Nested IF and COUNTIF Formula in Excel****[Fixed] COUNTIF Function with Wildcard Not Working in Excel****COUNTIF Function to Count Cells That Are Not Equal to Zero****Use Nested COUNTIF Function in Excel (6 Suitable Ways)****How to Apply Excel COUNTIF with Pivot Table Calculated Field****Difference Between SUMIF and COUNTIF Functions in Excel**