Today we will learn more about the Excel Countif Functions, precisely how to count cells that are not blank, more than or less than a given number, and how to count cells that contain a specified value. For most of us, counting cells in an Excel worksheet is a routine task when working on a spreadsheet. For example, counting the number of blank or non-blank cells, counting the number of cells greater than or less than a specific value, and counting the number of cells containing specific text are routine tasks when working on an Excel worksheet. When dealing with this kind of calculation, the COUNIT function in Microsoft Excel may aid you. We will be learning quite a few countif functions today, so stay tuned. Please make a note of these formulae, or bookmark this page for future reference to incorporate them into your work more efficiently.
Using Countifs To Count Blank Cell
Allow me to introduce you to the MS Excel Formula is known as COUNTIF for the time being. When utilizing this Formula, there is no limit to the number of data types included inside a range; it will always provide you with the exact and correct number of empty cells.
When working with massive spreadsheets, keeping track of how many blank cells there may be a critical chore. It may be almost impossible to count the number of blank cells in a vast spreadsheet. Count Blank Cell formula will come to your rescue in this situation. This simple Formula will assist you in counting the number of blank cells in a considerable number of cells with the least amount of work and time. In this case, the formula you’ll need is “=COUNTIF(range,””),” where range refers to the Column and cell numbers in which your data is included.
Step 1: Open your Microsoft Excel Spreadsheets and take a cell where you want to show the numbers of the blank cells.
Step 2: Modify the Formula according to your data set; you can go about it two ways. The first way is to input the Column and cell numbers manually. In our case, it is A2:A8. Secondly, you can click on the range and use the mouse to select the range of the data that you want the Formula to work on.
Step 3: After you have modified the formula, press enter to see the numbers of black cells in the range. In our small data range of A2 to A8, there are 3 empty or blank cells. So you can easily use this to count the number of blank cells.
Using Countifs To Count Non-Blank Cell
Like counting blank cells using the Countif function to calculate the blank cells in a range, you can also measure the Non-Blank Cells with the Countif function. All you’ll need is a bit modified formula for that. When working with extensive datasheets, it is also necessary to track how many cells are occupied. The ability to calculate the number of non-blank cells may be helpful in various scenarios. Because the Formula is “=COUNTIF(Range,”>”)”, all you need to do is input the Formula and the range of columns and cells from your data range to complete the task.
Step 1: Open your spreadsheets and choose a cell where you want the number of non-blank cells you want to be shown and enter the Formula.
Step 2: Change the data range. Follow the directions of our first function, step 2; after changing your range, all you need to do is press enter.
Step 3: Now, the amount of the Non-blank cells will be counted and shown in your cell where you entered the Formula. In our case, it’s 4.
Use Countif to Get Greater Than
To compute the larger than the value in a data range, we may utilize the countif function. This function is most often used to calculate when performance is necessary, such as when evaluating students or determining how sales objectives are met, among other things. It is possible to use this function to examine how the performances compare to the value we have set and see how many have surpassed it in an easy-to-understand manner. All we will need is the formula “=COUNTIF(Range,”>Number”)” where you will need to provide the range of your data collection as well as the number you want to use as a threshold.
Step 1: Enter Microsoft Excel open the data, and choose a cell where you wish to find the number of the cells containing the greater value.
Step 2: Enter the Formula, change the range and the number you need to calculate, and press enter to get the results for the greater than value. In our data set, the cells with a greater than the value of 60 came out to 5.
Use Countif to Get Less Than
This function may also be used to compute less than values in cells, and you can use it to calculate the less than values in cells. All of you students out there, your professors will utilize this feature to determine whether or not you will fail. All joking aside, the greater than and less than values are essential weapons in the arsenal of any financial analyst, and they may assist them in analyzing an enormous spreadsheet in a short period using them. All you need is the formula “=COUNTIF(Range,”Amount”)” to complete the task.
Step 1: Open your excel file with the data and choose a cell you wish for less than the number of cells shown. After selecting the cell, enter the Formula.
Step 2: After entering the Formula, adjust the range, and the amount and press enter to see the results. In our case, we calculated how many students scored less than 70, and 4 people scored less than 70.
Use Countif TO Look For Specific Value
We can also use the Countif function to search for specific values inside a colossal spreadsheet with many rows. Consider the following scenario: we are seeking all male pupils in a spreadsheet with information about the whole school. With a simple formula, we can quickly and determine this using the Countif. It is necessary to use the following Formula: “=COUNTIF(Range,”*Search Query*”)”. Specifying the desired range and the desired search query is required to get the desired results from the Formula quickly and simply.
Step 1. Open the Excel file, choose the cell where you wish the results to show, and enter the Formula.
Step 2: Adjust the range and the search query to your need and press enter. Here our data range is A2-A8, and our search query is e. The Formula will find out how many people have E in their names. Press enter to see the results.
Conclusion:
You now understand how to use EXCEL. The COUNTIF function counts the number of cells that are not blank and are larger than or equal to a particular value. You have learned quite a few functions for the Countif functions in this section. If you find it difficult to remember all of these formulae, please make a note of them or store them in a word document for future reference. These formulae will save you a significant amount of time and work.