Count Cells Less than a Value in Excel (COUNTIF Less)

Microsoft Excel is a powerful tool for data management and analysis.

Sometimes in Excel, we want to count cells that meet some predefined criteria, such as counting cells less than a specified value. 

For example, consider the following case where we have some stationary products’ daily sales data in USD. 

Let’s say we want to count the number of products having daily sales of less than 100 USD. So how can we actually do this? 

There are various methods you can use to achieve this.

For instance, using the COUNTIF function, a combination of SUM and IF functions, and by using the SUMPRODUCT function. 

Each of these methods has its advantages and drawbacks, and you can use any of these methods depending on your needs and requirements. 

The tutorial will guide you through all three methods to count cells in a range with a value less than a particular value.

Method 1: Count Cells Less Than a Value Using COUNTIF Function

This is the most straightforward built-in Excel function to count the number of cells based on a preset condition. 

The COUNTIF function works in a way by counting the number of cells in a specified range that fulfills the given criteria. 

In this section, I will show you how you can employ the COUNTIF function to count the number of products having daily sales of less than 100 USD. 

I am going to use the same dataset described in the introduction plus some additional cells for displaying count value which is shown below.

To count the number of products having daily sales less than 100, enter the below formula in cell D5 (where we want the result)

=COUNTIF(B2:B11,"<100")

Where B2:B11 represents the specified range and “<100” is the criteria based on which we are going to count cells. 

Remember that you need to keep the criteria in double-quotes.

In case you have the value (100 in this example) in a cell, and you want to refer to that cell instead, you can do that as well.

Assuming that you have the criteria value in cell D1, the formula would be:

 =COUNTIF(B2:B11,"<"&D1)

Here is how the formula works:

The syntax for the COUNTIF function is as follows:

 = COUNTIF(range, criteria)

• Range: range of cells that you want to count. In the above example, we want to count on sales, so the range is B2:B11.
• Criteria: condition based on which function counts the cell. In the above example, we want to count values less than $100, so the criteria are “<100”. Remember always to provide criteria in double-quotes. 

The advantage of using the COUNTIF function is that it is straightforward to use. 

You simply specify the range and the criteria, and it returns the count of cells that meet the criteria within that specified range. 

Additionally, the COUNTIF function is flexible, as it can be used to count cells with values less than a specific value, greater than a specific value, or equal to a specific value.

Also read: How to Count Between Two Numbers in Excel?

Method 2: Count Cells Less Than a Value Using SUM and IF Function

You can also use the SUM function in combination with the IF function to count the number of cells less than a value. Let’s see it in practice. 

For demonstration purposes, I am going to use the same dataset described in the previous method.

To count the number of cells with sales less than 100, you can use the following formula.

=SUM(IF(B2:B11 <100,1,0))

In the above example, the formula uses SUM and IF functions to count the number of cells having sales less than 100. 

1. The IF function checks each cell in the range (B2:B11) and returns 1 when the value is less than 100 and returns 0 when it is greater than or equal to 100. 
2. The SUM function then adds up all the values returned by the IF function. In the above example, we have 6 values less than 100, so the IF function returns 1 six times, which is then added by the SUM function to give the final result 6.

Also read: How to COUNTIF Partial Match in Excel?

Method 3: Count Cells Less Than a Value Using SUMPRODUCT Function

SUMPRODUCT is a built-in Excel function that is used to first multiply an array or range of numbers and then add up the result, but here we are going to use it to count the number of cells less than a specific value. 

Let me show by doing an example.

I am going to use the same dataset provided in the previous method, where we have some demo sales done by the stationery products.

Let’s see how we can use the SUMPRODUCT function to count the number of cells where the value is less than 100. To do so, the formula is described below.

=SUMPRODUCT((B2:B11<100)*1)

Write the formula in Excel as shown below

Let me now explain how this formula works:

1. The Expression (B2:B11<100) compares each value in the range B2:B11 with 100. If the value is less than 100, it returns True (1), and if the value is less than 100, it returns False (0). In this example, we get [0,0,0,0,1,1,1,1,1,1] from this expression.
2. (*1) – The 1/0 array is then multiplied by 1.
3. SUMPRODUCT() – Finally, the SUMPRODUCT function adds up the values in the array, giving the total count of cells which is 6 in this case.

In this tutorial, we reviewed different methods of counting cells less than a specific value in Microsoft Excel.

We looked at the COUNTIF, SUMPRODUCT, and combination of SUM and IF functions.

While the COUNTIF method is the most straightforward way to count cells with a value less than a specific value, it’s good to know the SUMPRODUCT and SUM/IF methods as they can allow you to do more in advanced formulas.

Other Excel articles you may also like:

• How to Count Cells with Text in Excel?
• COUNTIF Greater Than Zero in Excel
• How to Count Filtered Rows in Excel?
• How to Count Unique Values in Excel
• Count the Number of Yes in Excel (Using COUNTIF)
• How to Count Negative Numbers in Excel
• How to Count How Many Times a Word Appears in Excel
• Count Cells that are Not Blank in Excel (Formulas)