Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Count number of cells in list less than a certain value

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 10:00. Reason: remove code tags from formula

  • #2
    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

    Comment


    • #3
      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

      Comment


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

        Sorry about that

        Comment


        • #5
          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
          sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

          Comment


          • #6
            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

            Comment


            • #7
              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
              sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

              Comment


              • #8
                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

                Comment


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

                  No worries Oldman!

                  Take it easy

                  Smallman
                  sigpicthesmallman.com .......... Excel Dashboards ............ Excel Infographicsg..........k

                  Comment


                  • #10
                    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

                    Comment

                    Trending

                    Collapse

                    There are no results that meet this criteria.

                    Working...
                    X