Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif 0, <8
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif 0, <8
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:O450),--(O34:O45<8)) Note that I removed an extra parenthesis from your posted formula -- Regards, Peo Sjoblom "KatyCoxy" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif 0, <8
=sumproduct((o34:o450)*(o34:o45<=8))
-- Don Guillett Microsoft MVP Excel SalesAid Software "KatyCoxy" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif 0, <8
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 "Peo Sjoblom" wrote in message ... 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:O450),--(O34:O45<8)) Note that I removed an extra parenthesis from your posted formula -- Regards, Peo Sjoblom "KatyCoxy" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif 0, <8
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. -- David Biddulph "KatyCoxy" wrote in message ... 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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
countif 0, <8
Hi Katy,
Some minor points: 1. You should adjust Don's formula by changing <=8 to <8 in the formula =SUMPRODUCT((A1:A170)*(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:A170)*(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 "KatyCoxy" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
COUNTIF Help | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |