Skip to content

Formulas to Count the Cells Less than a Given Number in Excel!

Listen to this article.

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

Formulas to count cells that contain less than a particular value

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.


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.
  • – 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.

List of NamesPin
List of Names

After that, you can use the below-mentioned formula in any cell where you want to get the output result.

=COUNTIF(B3:B13, “William”)

Enter the formulaPin
Enter the formula

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.

Get Total OccurrencesPin
Get Total Occurrences

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.
Sample DataPin
Sample Data
  • 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.
Execute the formulaPin
Execute the formula
  • Then, you have to drag the first output cell to fill in the remaining results on the spreadsheet, as shown below.
Drag the formulaPin
Drag the formula
  • 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.
Select a cellPin
Select a cell
  • 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.
Formulas TabPin
Formulas Tab
  • 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.
Function Arguments dialoguePin
Function Arguments dialogue
  • 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.


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

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 websiteExcel 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:


Leave a Reply

Your email address will not be published. Required fields are marked *

Share to...