countif >0, <8

[Guest]

I tried to post this message yesterday but I'm not sure if it posted,
apologies if it comes up twice.

My question relates to the countif function. I want to count the numbers
that are greater than 0 and less than 8 in one column.

The formula that I have been trying to use, and variations therin is
=COUNTIF(O34:O45,">=0")-COUNTIF(O34:O45,"<8")

This doesn't give me the right answer. I have also tried a variation of the
answer posted to Sassy on 6/14/2007.

Any help would be greatly appreciated.

 

[Peo Sjoblom]

I gave you 2 perfectly fine solutions yesterday why don't you check your
original post?
Here's what I wrote

You need to change the logic since you are not using AND functionality

=COUNTIF(O34:O45,">0")-COUNTIF(O34:O45,">=8")



this is uses AND functionality and will give the same result


=SUMPRODUCT(--(O34:O45>0),--(O34:O45<8))


Note that I removed an extra parenthesis from your posted formula

 

[Don Guillett]

=sumproduct((o3445>0)*(o3445<=8))

 

[Peo Sjoblom]

Sorry I didn't see your apology, I apologize for telling you to check your
first post. Anyway, hopefully you will see this post


--


Regards,


Peo Sjoblom

 

[David Biddulph]

Yes, your question did come up before and you got an answer. If you use a
newsreader like OE you can sort the messages by the From field to find the
thread, then follow the thread to get the answer. Otherwise you can search
the archives of the group at Google.

If your current method of accessing the group doesn't let you find the
answer, then you are probably not using a satisfactory method of accessing
the newsgroup.

In the meantime, look again at your formula and ask yourself what each half
of the formula is doing. If you are still struggling you can split the
formula in two and see what answer each half is giving, and compare it with
what you expect from your data and what you are trying to achieve. [If you
are still struggling with the logic, you might draw yourself a simple Venn
diagram of the situation?]

You could also look again at Sassy's question and the answer, and compare it
with your question.

 

[Guest]

Hi Katy,

Some minor points:
1. You should adjust Don's formula by changing <=8 to <8 in the formula
=SUMPRODUCT((A1:A17>0)*(A1:A17<=8))
2. If you are using 2007 you can write
=COUNTIFS(A1:A17,">0",A1:A17,"<8")
3. If you are dying to use COUNTIF in 2003 or earlier here is another
approach:
=SUM(COUNTIF(A1:A17,{">0",">=8"})*{1,-1})
4. You can write a shorter array version of formula 1 as follows:
=SUM((A1:A17>0)*(A1:A17<8))
This last one requires array entry (press Ctrl+Shift+Enter instead of Enter)

Most likely the COUNTIFS function executes fastest but I haven't checked it,
of course it only works in 2007. Formula 4 generally executes 10-15% slower
than formula 3 but that is only an issue if you have lots of formulas that
slow down your spreadsheet.

Cheers,
Shane Devenshire