In this article, I will tell you how to use the excel formula to count less than and/or greater than operations with examples.
1. How To Count Less Than And Greater Than In Excel Example.
- Because the Excel COUNTIFS function will return the number that matches both the multiple criteria, we will use the function COUNTIFS to implement this example.
- Below are the example data cells. Column A contains the student’s name, column B contains the student’s score, and column C contains the student’s sex data.
A B C D E F 1 Student Name Score Sex Statistics 2 Jerry 100 Male Score less than 90 3 Kate 90 Female Score greater than 60 4 Tom 80 Male 5 Lily 60 Female 6 john 70 Male 7 Richard 80 Male 8 Jackie 90 Female Count 3 - Input the formula =COUNTIFS(B2:B8,”<“&F2, B2:B8,”>”&F3) in cell F8 and press the enter key, then it will display number 3 in cell F8.
- Because there are 3 cells ( B4, B6, B7 ) in the cell range B2:B8 match the criteria.
- In the above formula, B2:B8 is the score number cell range, we will compare the criteria to this cell range numbers.
- The criteria “<“&F2 are used to count the numbers ( in the above cell range ) that are less than the number in cell F2.
- The criteria “>”&F3 are used to count the numbers ( in the above cell range ) that are greater than the number in cell F3.
- If you want to count the number less than and equal to you can contact the string “<=” with the criteria cell number value.
- If you want to count the number greater than and equal to you can contact the string “>=” with the criteria cell number value.
2. How To Count Less Than Or Greater Than In Excel Example.
- Now we will use the excel function COUNTIF to count the cells that are less than or greater than the criteria.
- Below are the example data cells.
A B C D E F 1 Student Name Score Sex Statistics 2 Jerry 100 Male Score less than 80 3 Kate 90 Female Score greater than 90 4 Tom 80 Male 5 Lily 60 Female 6 john 70 Male 7 Richard 80 Male 8 Jackie 90 Female Count 3 - Input the formula =COUNTIF(B2:B8,”<“&F2)+COUNTIF(B2:B8,”>”&F3) in the cell F8, then it will display the number 3 in cell F8.
- The function COUNTIF only takes 2 parameters, the first parameter is the cell range, and the second parameter is the criteria.
- The “<“ character will count the number that is less than the value, the “>” character will count the number that is greater than the value.
- We plus the above 2 numbers to count the number that is less than or greater than the value.