COUNT Less Than (COUNTIF – COUNTIFS) in Excel

COUNTIF to Count Less Than Cells

In the following example, we have a list of values in the column (a list of 1000 numbers). Now, you need to count the cells which are less than 45. As I said, you need to use COUNTIF, and we will write this formula in cell B1.

You can use the following steps:

1. First, start typing the COUNTIF function in cell B1 (Enter =COUNTIF).
2. After that, in the first argument of the function, refer to column A. You can also refer to A1:A101 only.
3. Now, type a comma to go to the second argument. Here, you need to specify the criteria. So, type <=45 with double quotation marks (or <45 if you don’t want to count 45 itself).
4. In the end, type the closing parenthesis and hit enter.

=COUNTIF(A:A,"<=45")

And once you hit enter, it returns the count of cells with values less than or equal to 45.

You can also specify a cell that contains the criteria value. In the following example, you have a value in cell B1 that you want to use as criteria and then refer to the cell in the function.

Now if you see, we have the formula in cell B4. In this formula, we have referred to the range A1:A101.

And in the second argument, where we need to specify the criteria, we have entered the less than or equal to operator using double quotation marks.

After that, we used an ampersand and referred to cell B1.

This way, if you want to change the criteria, you don’t need to change the value in the formula itself; you can edit it in cell B1.

=COUNTIF(A1:A101,"<="&B1)

The operator stays inside the quotation marks, and the ampersand joins it with the value from B1. If you put the cell inside the quotes, like “<=B1”, COUNTIF looks for the text “B1” and returns 0.

Points to Remember

1. When you use <=, Excel takes it as lower than and equal to. So, if you use <=45, this means all values which are lower than or equal to 45. But if you use only < that means you are referring to lower-than values only, for example, <45 means values less than 45.
2. You can also create multiple criteria by using a COUNTIFS function and apart from this you can use COUNTIF OR in Excel to create an or logic while counting the less than values.
3. Always enclose your criteria in quotes when they are logical operators (like <, <=, >, >=). For example, “<40” means you want to count numbers less than 40.
4. COUNTIF ignores non-numeric data when counting for “<40”. These entries won’t be counted if the data column includes text or error values, and that includes numbers stored as text. Blank cells aren’t counted either.

Next Read – Excel COUNT IF Greater Than 0 / Excel COUNTIF Blank / COUNTIF Cell is Not Blank

COUNTIFS Less Than (Multiple Conditions to Count Less Than)

Now let’s suppose you have data with student names, genders, and scores. If you want to count the number of female students who scored below 40, the COUNTIFS function is exactly what you need.

The below formula helps you quickly count the number of female students scoring below 40:

=COUNTIFS(B2:B21,"Female",C2:C21,"<40")

It checks two things: first, it looks in column B to see if the gender is “Female”; second, it looks in column C to find scores less than 40. Only when both conditions are met (a female student who scored below 40) does it count that row.

Use SUMPRODUCT to Count Less Than Values

The same count can be calculated with SUMPRODUCT.

=SUMPRODUCT(--(A1:A100 < 45))

• (A1:A100 < 45) – This part of the formula creates a condition that checks each cell in the range A1 through A100 to see if it contains a value less than 45.
• — The double minus (–) converts TRUE and FALSE into 1 and 0, respectively; TRUE becomes 1, and FALSE becomes 0.
• SUMPRODUCT(–(A1:A100 < 45)) – SUMPRODUCT sums all the 1s and 0s. Since each 1 represents a value less than 45, the sum is the total count of such values below 45.

There’s one difference from COUNTIF: SUMPRODUCT treats a blank cell as 0, and 0 is less than 45, so blank cells get counted too. To count only the numbers, add ISNUMBER as a second condition:

=SUMPRODUCT(--ISNUMBER(A1:A100),--(A1:A100<45))

Next Read – COUNTIF Not Equal To / COUNTIF OR / COUNTIFS Between Two Numbers

And if you want to count less than using multiple conditions, you can use a formula like the following:

=SUMPRODUCT((B1:B100="Female")*(C1:C100<40))

It works by checking each entry in column B to see if it says “Female”, creating an array of TRUE or FALSE values.

Then, it checks each entry in column C to see if the score is less than 40, creating another array of TRUE or FALSE values. These arrays are then multiplied, where TRUE values (represented as 1) from both conditions produce a 1 (since 1*1=1), and all other combinations result in a 0.

Then, it sums these values, giving the count of females who scored less than 40.

Wrap Up

• Use COUNTIF if you only want to count how many test scores are below 45.
• Use COUNTIFS if you want to count scores that are below 40 and also add one more condition.
• Use SUMPRODUCT when you want to count values with more complex calculations.

Count Dates Less Than a Date

COUNTIF works the same way with dates, as dates are numbers in Excel. Use the DATE function in the criteria, so the formula works with any date format. The following formula counts the dates in A2:A100 that are before 1 January 2024:

=COUNTIF(A2:A100,"<"&DATE(2024,1,1))

And to count the dates before today, use TODAY() instead of DATE:

=COUNTIF(A2:A100,"<"&TODAY())

Frequently Asked Questions