CountIf Question

multi-lingual_ooo · OOo Advocate Joined: 06 Nov 2004 Posts: 458

In my spreadsheet have a number of equations like the following:
=COUNTIF(B827:B926;">20")

What I'd like to do, is replace the expression ">20" with a cell reference.
EG: =COUNTIF(B27:B926;">A100")

The value ">20" will change, and I'd like to spare myself the agony of changing several hundred equations manually.

Does anybody have any pointers on how to put a cell reference into the COUNTIF expression?
Or how to write a function that duplicates COUNTIF, but allows for cell references?

xan

jonathon
_________________
One of the following might contain more details:

http://oooauthors.org/en/members/tutorials/multilingualooo/
http://esnips.com/web/OOoRelatedThings/
http://esnips.com/web/GraphologyTools

Sliderule · Posted: Fri Jun 22, 2007 12:55 pm Post subject:

jonathon:

There are a few ways ( take your choice ) to make the alterations you want . . . depending on your needs.

IF A100 contains a number then =COUNTIF(B827:B926; ">" & A100)
IF A100 contains ">20" THEN =COUNTIF(B827:B926; A100)
IF A99 contains ">" and A100 contains 20 THEN =COUNTIF(B827:B926; A99 & I2)

I hope this helps, please be sure to let me / us know.

Sliderule

multi-lingual_ooo · OOo Advocate Joined: 06 Nov 2004 Posts: 458