Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with non-continous ranges
Hi all, I'm reading this site and am really impressed with the knowledge. I'm
hoping I can get a simple answer for a simple mind. I am just learning formulas so much of what I see for answers I just don't truly understand. Here is the formula I have set up. What I am trying to do is count U5:U10 + U12:U17. I do actually get the correct results with this following set up, but I just think I'm being lucky. I can get the set up work correct in the CountA function, but not the CountIF... =COUNTIF(U5:U10:U12:U17,"3.028")/COUNTA(U5:U10,U12:U17) Thanks so much for your help. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with non-continous ranges
One way
=SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17"}) ,3.028))/COUNTA(U5:U10,U12:U17) or =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17"}) ,3.028))/COUNT(U5:U10,U12:U17) since COUNT count numbers only while COUNTA count numbers and text -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Debbie" <u21726@uwe wrote in message news:600d62a5a3565@uwe... Hi all, I'm reading this site and am really impressed with the knowledge. I'm hoping I can get a simple answer for a simple mind. I am just learning formulas so much of what I see for answers I just don't truly understand. Here is the formula I have set up. What I am trying to do is count U5:U10 + U12:U17. I do actually get the correct results with this following set up, but I just think I'm being lucky. I can get the set up work correct in the CountA function, but not the CountIF... =COUNTIF(U5:U10:U12:U17,"3.028")/COUNTA(U5:U10,U12:U17) Thanks so much for your help. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with non-continous ranges
Peo, thank you for the quick response, since I new to this may I ask what
does INDIRECT mean? and based on what I've read isn't SUMPRODUCT a mutiplier function? Thank you again for your time on this. Peo Sjoblom wrote: One way =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17"} ),3.028))/COUNTA(U5:U10,U12:U17) or =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17"} ),3.028))/COUNT(U5:U10,U12:U17) since COUNT count numbers only while COUNTA count numbers and text Hi all, I'm reading this site and am really impressed with the knowledge. I'm [quoted text clipped - 8 lines] =COUNTIF(U5:U10:U12:U17,"3.028")/COUNTA(U5:U10,U12:U17) Thanks so much for your help. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with non-continous ranges
It's funny I get the same results no matter what formula I use the one you
sent and mine, I just look at mine and it doesn't appear logical... Peo Sjoblom wrote: One way =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17"} ),3.028))/COUNTA(U5:U10,U12:U17) or =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17"} ),3.028))/COUNT(U5:U10,U12:U17) since COUNT count numbers only while COUNTA count numbers and text Hi all, I'm reading this site and am really impressed with the knowledge. I'm [quoted text clipped - 8 lines] =COUNTIF(U5:U10:U12:U17,"3.028")/COUNTA(U5:U10,U12:U17) Thanks so much for your help. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with non-continous ranges
INDIRECT will make the 2 ranges readable by turning it into string that
COUNTIF can read and turn into a 2 element array (you are using 2 different ranges, if there were 3 you would get a 3 element array, but COUNTIF can only count the first part of the array so if you would use COUNTIF(INDIRECT({"U5:U10","U12:U17"}),3.028) and you had 2 occurrences in U5:U10 and 2 in U12:U17 the array would look like {2,2} but it would only return 2 not 4 but if you use either SUM or SUMPRODUCT it would return 4 since it would sum each array element -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Debbie" <u21726@uwe wrote in message news:600ddca90e555@uwe... Peo, thank you for the quick response, since I new to this may I ask what does INDIRECT mean? and based on what I've read isn't SUMPRODUCT a mutiplier function? Thank you again for your time on this. Peo Sjoblom wrote: One way =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17" }),3.028))/COUNTA(U5:U10,U12:U17) or =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17" }),3.028))/COUNT(U5:U10,U12:U17) since COUNT count numbers only while COUNTA count numbers and text Hi all, I'm reading this site and am really impressed with the knowledge. I'm [quoted text clipped - 8 lines] =COUNTIF(U5:U10:U12:U17,"3.028")/COUNTA(U5:U10,U12:U17) Thanks so much for your help. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with non-continous ranges
It's because you aren't really using multiple ranges
=COUNTIF(U5:U10:U12:U17,"3.028") is the same as =COUNTIF(U5:U17,"3.028") you can test that by putting 3.028 in U11 and notice that it will be counted, but =SUM(COUNTIF(INDIRECT({"U5:U10","U12:U17"}),3.028) ) will not count 3.028 in U11 but if U11 is blank the result would be the same -- Regards, Peo Sjoblom http://nwexcelsolutions.com "Debbie" <u21726@uwe wrote in message news:600e02923e903@uwe... It's funny I get the same results no matter what formula I use the one you sent and mine, I just look at mine and it doesn't appear logical... Peo Sjoblom wrote: One way =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17" }),3.028))/COUNTA(U5:U10,U12:U17) or =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17" }),3.028))/COUNT(U5:U10,U12:U17) since COUNT count numbers only while COUNTA count numbers and text Hi all, I'm reading this site and am really impressed with the knowledge. I'm [quoted text clipped - 8 lines] =COUNTIF(U5:U10:U12:U17,"3.028")/COUNTA(U5:U10,U12:U17) Thanks so much for your help. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with non-continous ranges
Peo Sjoblom wrote...
.... or =SUMPRODUCT(COUNTIF(INDIRECT({"U5:U10","U12:U17"} ),3.028)) /COUNT(U5:U10,U12:U17) since COUNT count numbers only while COUNTA count numbers and text .... If these were all numbers, there's an alternative that avoids using the volatile function INDIRECT. =INDEX(FREQUENCY((U5:U10,U12:U17),3.028-{1E-12,0}),2)/COUNT(U5:U10,U12:U17) Then again, there's only one excluded cell, so brute force isn't that difficult. =(COUNTIF(U5:U17,3.028)-COUNTIF(U11,3.028))/(COUNT(U5:U17)-COUNT(U11)) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif with non-continous ranges
aaahh, THANK YOU very much for explaining this way, now I understand!
Peo Sjoblom wrote: It's because you aren't really using multiple ranges =COUNTIF(U5:U10:U12:U17,"3.028") is the same as =COUNTIF(U5:U17,"3.028") you can test that by putting 3.028 in U11 and notice that it will be counted, but =SUM(COUNTIF(INDIRECT({"U5:U10","U12:U17"}),3.028 )) will not count 3.028 in U11 but if U11 is blank the result would be the same It's funny I get the same results no matter what formula I use the one you sent and mine, I just look at mine and it doesn't appear logical... [quoted text clipped - 15 lines] =COUNTIF(U5:U10:U12:U17,"3.028")/COUNTA(U5:U10,U12:U17) Thanks so much for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF using # ranges, and referencing 2 other columns | Excel Worksheet Functions | |||
COUNTIF with multiple disjoint ranges, same criteria | New Users to Excel | |||
COUNTIF with multiple disjoint ranges, same criteria | New Users to Excel | |||
Countif between ranges | Excel Discussion (Misc queries) | |||
COUNTIF criteria using date ranges? | Excel Worksheet Functions |