COUNT Less Than (COUNTIF – COUNTIFS) in Excel

- Written by Puneet

excelchamps-free-courses-puneet-gogia

If you have a list of student’s scores and you want to find out how many scored below 45, you can use the COUNTIF. With this function, you can specify a range of values to count and then a criteria number to count the cells less than that.

You can also use COUNIFS to count less than values if you want to use multiple conditions. This tutorial teaches you how to write formulas to count less than values. So let’s get started.

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, we will write this formula in cell B1.

list-of-values

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 the A1:A101 only.
  3. Now, type a comma and get in the second argument. From here, you need to specify the argument for the criteria. So, type <=45 with double quotation marks.
  4. In the end, type the closing parentheses and hit enter.
countif-function-used

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

count-of-values-as-per-criteria

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.

countif-with-criteria

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 greater than and equal to the 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 from the formula itself, you can edit it from cell B1.

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 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 ignore non-numeric data when counting for “<40”. These entries won’t be counted if the data column includes text or error values.

COUNIFS Less Than (Multiple Conditions to Count Less Than)

Now let’s suppose, you have a 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 takes a count.

Use SUMPRODUCT to Count Less than Values

The same count can be calculated by the 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 A101 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 sum of 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.

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, COUNTIF would be perfect.
  • Use COUNTIFS, if you want to count scores that are not only below 65 but also want to add one more condition.
  • Use SUMPRODUCT, when you want to count values with more complex calculation.
Last Updated: May 03, 2024

Leave a Comment