Microsoft Excel is the most powerful spreadsheet program that helps to perform various mathematical calculations. For that, it offers huge built-in formulas and functions which make our work easy.
On that note, if you want to count the cells based on some condition on your worksheet, then Excel has a special feature for it. You can use the “COUNTIF” formula. From the name itself, we can understand that it will count the cells based on the given “If Statement” (condition). Here in this tutorial, we will show you the formula & its explanation, and a few examples for your better understanding. Let’s see how to count cells in the spreadsheet based on a given condition using the COUNTIF function.
https://www.microsoft.com/en-in/microsoft-365/excel
Jump To
General Formula
Here, we come up with a simple formula to count the number of cells that have values less than a given number on your worksheet.
To count the number of cells that have values less than a given number in Excel, you can use the below formula.
=COUNTIF(range,”<X”)
Explanations for Syntax
In this section, we provide the explanation for the above-given formula, so that you can understand it more clearly.
- COUNTIF – In Excel, COUNTIF Function will help to count the cells that match a single condition or criteria.
- Operator (<) – This operator symbol will count less than a particular value. If you count the cell which is less than or equal to the given value, use the (<=) symbol.
- Range – It represents input numbers given in the workbook.
- X – It is a value you that want to be counted.
- Comma (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
How to Apply COUNTIF Formula?
As we already said, the COUNTIF function helps to find out the total number of occurrences of a specific value or count the number of cells that meets certain criteria. For instance, In an organization, there are 500 people currently working and some of them have the same name. Suppose you want to find the total number of persons who are working in the organization with the name “William‘, then we suggest you use the COUNTIF function.
In this scenario, to get the total count of the cells which has the name “William, select the range where you want to find out the count.
After that, you can use the below-mentioned formula in any cell where you want to get the output result.
=COUNTIF(B3:B13, “William”)
Once you have entered the formula, click on the Enter button to execute it and get the result. We hope that this scenario helps you understand the basic concept of the Excel COUNTIF formula.
We hope that the above-given scenario makes you understand the working nature of the Excel COUNTIF formula. If you still have some doubts and want to learn it with simple examples. Then jump to the upcoming section where you can see alternate examples which will definitely help you.
Alternate Examples
By doing the following instructions, you will find the values that are less than the specific value on your Excel worksheet. Do the steps carefully.
- Initially, you have to open your Excel workbook on your computer with the sample data or information.
- Here, we have shown the sample data that we have entered on the spreadsheet.
- In the above image, you will see the input ages in Column A.
- Once you have the data on the spreadsheet, you have to apply the given formula in the appropriate cell.
- We entered the formula in Cell E2 and hit the Enter button to get the result.
- Then, you have to drag the first output cell to fill in the remaining results on the spreadsheet, as shown below.
- By doing the above instructions, you can easily count the cells which contain less than a given number in Excel.
Another Possible Way
In order to type the formula manually, you can also select it from the menu and complete the task quickly.
- For that, you need to open the Excel worksheet that has data. Here we will count the number of students who get scores less than 50 marks using the COUNTIF formula.
- You need to select a cell where you want to get the result.
- Now, go to the Formulas Tab, and click on the More Functions drop-down list under the Function Library section.
- From the drop-down menu, you have to select the Statistical option and then choose the COUNTIF option from the sub-menu, as shown in the below image.
- It will display the Function Arguments dialogue, where you need to give the input range and criteria using the given browse button.
- Then, hit the OK button to get the result on the specific cell on your spreadsheet.
- Finally, you will get the output count value in the selected cell, as shown in the below image.
This is another way to use the COUNTIF formula in the Excel spreadsheet. Hope it is useful to you.
Conclusion
So far, we can understand that the Excel COUNTIF formula is a peculiar tool that helps to find out the number of cells that meets certain criteria in the specified range. The given sample scenario and examples let you use it on your worksheet without any mistakes. By applying any of the above-mentioned two ways, you can save time and get the proper output. If you want to learn more Excel formulas, then visit our webpage Aawexcel.com.
To know more about this formula, we suggest you look for it on the Quora interaction webpage. On that page, you will get to know what others think about the Excel COUNTIF formula. Here, we have provided the link to Quora’s official website: Excel COUNTIF Function.
Video Tutorial
The following video will let you know how to apply the COUNTIF formula in the worksheet in order to get the total count of cells based on some conditions crisp and clear.
Also Read:
- How to Use Excel FLOOR.MATH Function?
- How to Use Excel FLOOR Function?
- How to Use Excel CEILING.PRECISE Function?
- How to Use Excel CEILING Function?
- How to Use Excel GCD Function?
Hi there, I’m Sridhar – an Excel enthusiast with over 10 years of experience working with software. I’m passionate about using Excel to solve complex problems and streamline business processes. Over the years, I have helped businesses of all sizes to improve their operations and save time and money.
Aside from working with Excel, I also enjoy writing and sharing my knowledge with others. You’ll often find me contributing to the AAW Excel blog, where I provide tips, tricks, and tutorials that are easy to understand for readers of all skill levels.
