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

[Solved] COUNTIF value between two values?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
feathin
Newbie
Newbie


Joined: 17 Oct 2011
Posts: 3

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

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
Back to top
View user's profile Send private message
pitonyak
Administrator
Administrator


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

PostPosted: Mon Oct 17, 2011 8:37 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
feathin
Newbie
Newbie


Joined: 17 Oct 2011
Posts: 3

PostPosted: Mon Oct 17, 2011 8:47 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Tue Oct 18, 2011 1:58 am    Post subject: Reply with quote

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


Ken Johnson
Back to top
View user's profile Send private message
gerard24
OOo Enthusiast
OOo Enthusiast


Joined: 08 Jul 2011
Posts: 100
Location: France

PostPosted: Tue Oct 18, 2011 4:02 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
feathin
Newbie
Newbie


Joined: 17 Oct 2011
Posts: 3

PostPosted: Tue Oct 18, 2011 4:11 pm    Post subject: Reply with quote

Thanks Ken and Gerard, those both work perfectly.
Back to top
View user's profile Send private message
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