Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

1. Senior Member
Join Date
14th April 2011
Location
California
Posts
137

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

Excel Video Tutorials / Excel Dashboards Reports

2. Senior Member
Join Date
14th April 2011
Location
California
Posts
137

## 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. ## 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.

4. Senior Member
Join Date
14th April 2011
Location
California
Posts
137

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

Excel Video Tutorials / Excel Dashboards Reports

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

Excel Video Tutorials / Excel Dashboards Reports

6. Senior Member
Join Date
14th April 2011
Location
California
Posts
137

## 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. ## 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

Excel Video Tutorials / Excel Dashboards Reports

8. Senior Member
Join Date
14th April 2011
Location
California
Posts
137

## 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. ## 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. ## 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.

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

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