Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Count number of cells in list less than a certain value

  1. #1
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    Count number of cells in list less than a certain value

    Hi

    I have a large list. I need a formula that will count all the values in the list that are lower than a certain value. I tried to use the following formula but that returned the total for the full list.

    {=COUNTA(IF('sheet1'!G4:G500<20000,'sheet1'!G4:G500,0)}


    Anyone know where I went wrong?

    Thanks
    Last edited by AAE; January 24th, 2012 at 09:00. Reason: remove code tags from formula

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    Re: Count number of cells in list less than a certain value

    I made things too complicated.... use countif

    http://www.ozgrid.com/Excel/count-if.htm

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Count number of cells in list less than a certain value

    Re: post #1

    Please do not use code tags on formula. They are reserved only for use when posting VBA code.

    Thanks.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  4. #4
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    Re: Count number of cells in list less than a certain value

    Sorry about that

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,004

    Re: Count number of cells in list less than a certain value

    Hi Oldman

    It is not as complicated as the formula you are using. Think simple to start with and build from there.

    =COUNTIF(Sheet1!$G$4:$G$500,"<20000")

    Take care

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    Re: Count number of cells in list less than a certain value

    That's why I come to this board - I've learned enough to be dangerous but I need people like you Smallman to keep me in check!

    I've got a new problem branching off of this, but I can't seem to get it to work just right.

    So I have another column (call it A) that is filled with 1 or 0. How do I count the number of rows where column A = 1 and column G is between 20,000-40,000 (or you could just say <20,000 to keep it simple)?

    Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,004

    Re: Count number of cells in list less than a certain value

    Hi Oldman

    I am sorry I did not see your earlier response showing you had a solution.

    What version of XL are you on? If you are using XL 2007 or later the following is a workable solution for you.

    =COUNTIFS($A$2:$A$10,1,$B$2:$B$10,">20000",$B$2:$B$10,"<40000")

    I have attached a file just in case you have a recent version of XL.

    Take care

    Smallman
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    14th April 2011
    Location
    California
    Posts
    133

    Re: Count number of cells in list less than a certain value

    Don't worry about showing the formula twice... I need all the reinforcement that I can get

    Luckily I have xl 2011 so that formula works. Thanks for making things simple!

    You're helps been very much appreciated today, Smallman. Thank you

    Oldman

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    18th August 2011
    Location
    Brisbane, Austalia
    Posts
    3,004

    Re: Count number of cells in list less than a certain value

    No worries Oldman!

    Take it easy

    Smallman

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Count number of cells in list less than a certain value

    If want the criteria in the formula to be dynamic, you can use this version:

    =COUNTIFS($A$2:$A$10,1,$B$2:$B$10,">"&A1,$B$2:$B$10,"<"&B1)

    Where cells A1 and B1 are input cells.
    Obviously, adjust the references as required.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Count number of unique items in list
    By booo in forum EXCEL HELP
    Replies: 2
    Last Post: August 20th, 2011, 10:13
  2. Count Cells By Number & Add Adjacent Cell If Number Is X
    By black knight in forum EXCEL HELP
    Replies: 6
    Last Post: January 19th, 2008, 07:42
  3. Count Number Of Each Item In List
    By Ed_Kerrigan in forum EXCEL HELP
    Replies: 4
    Last Post: October 11th, 2007, 11:07
  4. Count number of items in list.
    By sdm in forum EXCEL HELP
    Replies: 19
    Last Post: February 7th, 2005, 16:22
  5. Replies: 3
    Last Post: December 30th, 2004, 12:00

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno