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.

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.

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.

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.

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.

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.

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))

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.