| View previous topic :: View next topic |
| 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 |
|
| Back to top |
|
 |
pitonyak
Administrator
Joined: 09 Mar 2004
Posts: 3622
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1874
Location: Sydney, Australia
|
| Posted: Tue Oct 18, 2011 1:58 am Post subject: |
|
|
| Code: | | =SUMPRODUCT(B26:B15000>30000;B26:B15000<50000) |
Ken Johnson |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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. |
|
| Back to top |
|
|
|
