Count Cells Greater Than Set Amount With Excel COUNTIF Function

Count Cells Greater Than Set Amount With Excel COUNTIF Function

One of the tasks you have to do quite often in Excel is to count things. Here’s how to count cells greater than set amount with Excel COUNTIF function.

Student Test Scores

In this example, we have a list of students and their test scores.

CountifOp00

If the passing score is 50, how can you get Excel to count the number of students who passed the test?

 Use COUNTIF and an Operator

The Excel COUNTIF function will count cells, based on the criteria that you enter. As part of the criteria, you can use an operator, such as greater than, or less than, to count a specific range of numbers.

In this example, the passing score is 50. To find the students who failed the test, you’d count the scores that are less than 50 — <50.

To include this operator in the COUNTIF criteria, enclose the operator and number in double quote marks.

=COUNTIF(B2:B11,”<50″)

CountifOp01

Use a Cell Reference with COUNTIF Criteria

Instead of typing a number in the COUNTIF criteria, you can use a cell reference. In this example, cell F5 contains the passing score.

To find the students who passed the test, you’d count the scores that are greater than or equal to the value in cell F5 — >=F5.

To include a cell reference and operator in the COUNTIF criteria, enclose the operator in double quote marks, and use an ampersand before the cell reference.

=COUNTIF(B2:B11,”>=” & F5)

countifop02

Cell Reference Automatically Updates

An advantage to using a cell reference in the COUNTIF criteria is that the result will automatically update, if the value in the referenced cell changes.

In the screenshot below, you can see that the passing score has been increased from 50 to 70. Because the Passed formula uses a reference to cell F5, the count is automatically updated.

The Failed formula has the passing score of 50 typed into the criteria, so that value would have to be changed, before the result is updated.

Watch the COUNTIF Video

To see the steps for creating a COUNTIF formula with an operator, you can watch this short Excel tutorial video.

_________________________

37 thoughts on “Count Cells Greater Than Set Amount With Excel COUNTIF Function”

  1. Hi Debra,

    When using a cell reference in the COUNTIF formula, you could enter the >= directly in the cell.
    So that in the above example F5 would be >=50 and the formula would be COUNTIF(B2:B11,F5).

    I have used the above with COUNTIF(xxx,xx) – COUNTIF(xxx,yy) to arrive at the count between two values
    Enjoy your blog very much, thanks for all the effort

    Regards

    Kanti

  2. Great Blog, thanks.

    Excel 2007 also has the additional function of COUNTIFS:

    =COUNTIFS(criteria range 1, criteria 1, criteria range 2 ….)

    This enables you to count the number of cells based on multiple criteria. An excellent addition from Microsoft.

  3. […] month we looked at the COUNTIF formula, and how you can type an operator, then refer to a worksheet cell, to set a minimum value for […]

  4. Thank you so much Debra, I’ve been working on this all day and went to numerous sites and chat rooms to find assistance with no luck, until i found your post… thank you so much, you are wonderful!

  5. Sorry, another quick question, I have Excel 2003 is there an alternative for countifs with multiple range options and multiple criterion or would i be best using sumproduct?

  6. Hi,
    need a help!!! i need to find the number of cells in a row which are greater than a certain value.
    problem is i my data is available in every fourth cell of the row. i am able to use the Mod and Array formula to arrive at the sum of these cells but finding the count is difficult

  7. Countif requires “>” & A1
    (replace > whit needed symbol > = <= and A1 whit apropriate cell\cells\ranges ect

  8. Hi Deb,
    I’m attempting to use the countif command to find the percent of my class that passed a test. With this command setup when 3 students pass, it returns 300%. Do I need additional data in the command such as dividing the passing students buy the total number of students. Hope that makes sense. And thanks in advance.
    =COUNTIF(H4:H28,”>=.7″)

    1. Did you get an answer for this? It is exactly what I am trying to do and what is happening to me?
      Thanks

    2. =COUNTIF(H4:H28,”>=.7″)/COUNT(H4:H28)
      Would give you count of passing scores divided by count of total scores which should be percent passed.

  9. HI, interesting article thanks. I have a COUNTIFS formula that works perfectly in Excel 2007 but had to save the spreadsheet for another user who only has Excel 2003. The results just return #NAME?. The EX07 formula is in B2 and counts a range of numbers in col A.
    COUNTIFS(A2:A100,”>=50″,A2:A100,”<=59"). How can I re-write this for Excel 2003. Thank you.

  10. HI,
    I’m trying to use a COUNTIFS formula in a spreadsheet to count the number of cells where the value is a range. I have 3 ranges
    Under 500 (easy as can use less than
    Over 1000 (easy as can use more than.
    Between 500 and 1000 – I’m stuck… Have tried =COUNTIFS(B$2:B$30,”NAME”,C$2:C$30,”>500:<1000") But it's not working. (I"ve also subbed a comma instead of colun.)
    Any ideas?

  11. How can I count the number of times the value of a cell equals to or is less than 0:26 minutes. The cell is formatted as Time 13:30.

    1. You could use the below formula as long as your cell is formatted as time.

      =COUNTIF(C5:C8,"<=0:26")
  12. Hi There, I am trying to perform a calculation using the datedif to count the days between two dates and if the total is less than 30 to enter 0 in that cell. Help ?

  13. I have times which I need to count how many are below a certain time
    each line below is an example
    12:59 AM,3:07 AM,2:51 AM,2:30 AM,12:06 AM times below 3:00 AM
    11:42 PM,12:05 AM,12:07 AM,10:57 PM,11:45 PM times below midnight
    1:05 AM 12:55 AM,1:03 AM,12:25 AM,1:00 AM times below 1:00
    as these are in 24 hour I cannot find a formula that works (I have tried countif but this gives false answers i believe its due to passing midnight)
    any help would be great
    Thanks Andy

  14. I am attempting to reference a date that correlates to the twentith count of another column. That’s number one problem.
    The second is the sum of the other 12 columns between those dates. Right now it looks like this sumif($L,”>0″,$B)
    This can easily become sumifs and include the dates but I want the dates to auto populate for every 20 items in column L.
    Column A are all dates.
    Column B have decimals.
    Column L is either 0 or 1.
    Thanks in advance.
    Jon

  15. Hello…I am using the countif formula (COUNTIF(E7, “>=” &G7)) to determine the number of PE students who meet a certain requirement on their fitness testing…students have to improve by 20% in all post-tests…here’s the problem…if a student equals the 20% requirement, then the formula doesn’t count…in fact, I changed the formula to “=” just to see what would happen and it wouldn’t count…any suggestions?

  16. I am hoping you can assist. I am trying to add any 4 numbers, where I have over 1000 numbers and reach a total of less or equal to 100. I want to know how many instances I can do this and how to identify the specific numbers. How do I write such a formula. Any assistance is appreciated. Thank you.

  17. I am trying to work out a formula , I have a countif statement, that counts the number of says some one attends to lets say the person can attend 1 day for £30 or up to 7 days at £210, if they attend all 7 days, I can then give them a discount of 10%..anyone help

  18. I am trying to work out a formula , I have a countif statement, that counts the number of Days someone attends a class, so lets say the person can attend 1 day for £30 or up to 7 days at £210, if they attend all 7 days, I can then give them a discount of 10%..anyone help

  19. Help needed please! I have to calculate how often a monitor reading exceeds a limit value (0.30) for more than three minutes. To be clear the continuous monitor is measuring values every 5 seconds and if the value exceeds 0.3 continuously for more than three minutes that counts as a fail. I download a huge data set each month two colums ‘Value’ and time

  20. I am working on a Sales spread sheet. That tracks sale date product codes and several other thing. I have a stats page that then takes the sale date and count the different products per month to see how much of each product is sold. The catch is that several of the products have multiple codes based on the different promotions the customer may get. Thos codes can also change often so I am trying to set up the Countif to have the products codes listed on another sheet and reference them for deciding what codes are counted. Here is an example of what the equation looks like right now.
    =(SUM(COUNTIFS(‘data new’!D2:D1114,”>=”&”2018-1-1″,’data new’!D2:D1114,”<"&"2018-2-1",'data new'!C2:C1114,{"abc","mno","4ho","EE("})))
    Where "abc","mno","4ho","EE(" are the codes that are for the same product just different prices based on what else was purchased.
    Is there a way to have a separate table that will say product A is "abc","mno","4ho","EE(" and link to that table so when a new code comes out I can just add the code to the table and not have to use find replace to edit the equations for all the months that are being tracked?

  21. I have a problem with the structure as bellow
    Col-A Col-B Col-C
    Row-1 NAME NUMBER PASS
    Row-2 BOB 50
    Row-3 JAMES 52
    Row-4 KELLY 40
    Row-5 ROGER 55
    Row-6 BILLY 35
    Row-7 ROBERT 55
    Row-8 MICHEL 25

    Here are some names and their scores in the Range $A$2 : $B$8.
    I want to get those names who scored Greater Than 40.
    I want to show them under “PASS” i.e. from Cell $C$2

    Need help… anybody pls…. thanking you

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.