How to Calculate Frequency Using COUNTIF Function in Excel

Get FREE Advanced Excel Exercises with Solutions!

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. 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 Function to Calculate Frequency in Excel

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.

how to calculate frequency in excel using countif

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.

Using COUNTIF with SUM Function to calculate frequency in Excel

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 the range argument.
    • “<=”&B18 → This refers to the criteria argument,
    • Output 6.
  • 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.

Final output of method 1 to calculate frequency using the COUNTIF function in Excel

Read More: How to Use COUNTIF for Non Contiguous Range in Excel


2. Calculating Frequency for Texts with Excel COUNTIF Function

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.

Calculating Frequency for Texts in Excel using COUNIF function

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.

Final output of method 2 to calculate frequency using the COUNTIF function in Excel

Read More: COUNTIF Between Two Cell Values in Excel


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.

to calculate frequency using the COUNTIF function in Excel

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.

Final output of method 3 to Count Frequency Using COUNTIFS Function in Excel

Read More: Excel COUNTIF to Count Cells Greater Than 1


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.

How to Find Frequency of a Range in Excel

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

Final output of method 4 to Find Frequency of a Range in Excel

Read More: How to Use COUNTIF Function with Array Criteria in Excel


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.

How to Count Frequency of Values in Multiple Columns in Excel

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.

Using COUNTIFS function to Count Frequency of Values in Multiple Columns in Excel

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.

Final output of method 5 to Count Frequency of Values in Multiple Columns in Excel

Read More: COUNTIF Function to Count Cells That Are Not Equal to Zero


Download Practice Workbook


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.


Related Articles


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

What is ExcelDemy?

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

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo