countif with non-continous ranges

[Debbie]

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 Sjoblom]

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]

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.

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.

 

[Debbie]

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

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]

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

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.

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]

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

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

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.

 

[Harlan Grove]

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

 

[Debbie]

aaahh, THANK YOU very much for explaining this way, now I understand!

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]