XP Pro SP3, XL 2000
I am trying to do something simple (I think) with countif but it wont have it.
I wish to to use a cell value as the countif criteria, e.g. countif(a1:a10,<b1) where cell 'b1' contains any numerical value.
I know countif does not allow this, so is there a work around using some other function to achieve this?
TIA
Zagra147
Not far off
=COUNTIF(A1:A10,"<"&B1)
or if the operator may change you could enter < 10 for ex in B1 and use
=COUNTIF(A1:A10,B1)
thereby allowing you alter operator dynamically also.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Bingo! Thanks. It works! I've been puzzling over that for a while, on and off. I knew there must be a simple explanation. It's such an obvious requirement.
One thing, though, cell 'b1' contains a formula referring to another cell value. To this formula I added &"g" so that 'b1' would read, e.g. '10g'. Your solution did not work with this until I removed the &"g". But I can live with that.
Thanks again.
Zagra147
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks