OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

CountIf Question

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
multi-lingual_ooo
OOo Advocate
OOo Advocate


Joined: 06 Nov 2004
Posts: 458

PostPosted: Fri Jun 22, 2007 12:36 pm    Post subject: CountIf Question Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Jun 22, 2007 12:55 pm    Post subject: Reply with quote

jonathon:

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

  1. IF A100 contains a number then =COUNTIF(B827:B926; ">" & A100)
  2. IF A100 contains ">20" THEN =COUNTIF(B827:B926; A100)
  3. 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
Back to top
View user's profile Send private message
multi-lingual_ooo
OOo Advocate
OOo Advocate


Joined: 06 Nov 2004
Posts: 458

PostPosted: Fri Jun 22, 2007 2:14 pm    Post subject: CountIf Question (Solved) Reply with quote

Sliderule wrote:

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


Thanks. I had no idea that any of those were possible.

I'll be using Option # 1 for the time being, but # 3 has some very interesting possibilites for future work.

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
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group