[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
feathin
Newbie

Joined: 17 Oct 2011
Posts: 3

Posted: Mon Oct 17, 2011 8:21 pm    Post subject: [Solved] COUNTIF value between two values?

Hi,

There's probably something very simple that I'm missing since I'm not extremely familiar with spreadsheeting, but I'm using the COUNTIF formula to count various instances in columns, and am having trouble figuring out how to count a value >x and <y.

Since this works fine:
 Code: =COUNTIF(B26:B15001;">30000")

I'd thought something along the lines of this would work:
 Code: =COUNTIF(B26:B15000;AND(">30000";"<50000"))

But it just returns #VALUE! (which incidentally can't be found anywhere in the Help, so I don't know what problem its trying to tell me that I have).

Last edited by feathin on Mon Oct 17, 2011 9:01 pm; edited 1 time in total
pitonyak

Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

 Posted: Mon Oct 17, 2011 8:37 pm    Post subject: The simple answer is that this is not supported. More specifically, you cannot use multiple criteria for a single countif. If you are creative, however, you may be able to do something similar by cheating a bit. For example, assume I want to know how many numbers are >=5 and <=7. Now, assume that I know the total number of numbers. In that case, I can take the total number of numbers, subtract the number that are <5 and subtract the number that are >7. Part of me things that you may be able to use count with a creative use of IF in an array formula or something similar, but I will not take the time to pursue that. DCount may also work, but I am skeptical._________________-- Andrew Pitonyak http://www.pitonyak.org/oo.php
feathin
Newbie

Joined: 17 Oct 2011
Posts: 3

 Posted: Mon Oct 17, 2011 8:47 pm    Post subject: Thanks Andrew, its a pity that a seemingly simple formula won't work. Thanks for your suggestions, it'll work fine for me to use the total number workaround, just a tad messier.
ken johnson
Super User

Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

Posted: Tue Oct 18, 2011 1:58 am    Post subject:

 Code: =SUMPRODUCT(B26:B15000>30000;B26:B15000<50000)

Ken Johnson
gerard24
OOo Enthusiast

Joined: 08 Jul 2011
Posts: 100
Location: France

Posted: Tue Oct 18, 2011 4:02 am    Post subject:

If your version is 3.0 or >, you can use COUNTIF as an array formula :

 Code: =COUNTIF((B26:B15000>30000)*(B26:B15000<50000);1)

This formula is validate with Ctrl+Shift+Enter.
_________________
LibreOffice 3.5.0 on Windows Vista
feathin
Newbie

Joined: 17 Oct 2011
Posts: 3

 Posted: Tue Oct 18, 2011 4:11 pm    Post subject: Thanks Ken and Gerard, those both work perfectly.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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