software

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,quot;3.028quot;)/COUNTA(U5:U10,U12:U17)
Thanks so much for your help.

One way

=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;}) ,3.028))/COUNTA(U5:U10,U12:U17)

or

=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;}) ,3.028))/COUNT(U5:U10,U12:U17)

since COUNT count numbers only while COUNTA count numbers and text

--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Debbiequot; lt;u21726@uwegt; wrote in message news:600d62a5a3565@uwe...
gt; Hi all, I'm reading this site and am really impressed with the knowledge.
gt; I'm
gt; hoping I can get a simple answer for a simple mind. I am just learning
gt; formulas so much of what I see for answers I just don't truly understand.
gt; Here is the formula I have set up. What I am trying to do is count U5:U10
gt;
gt; U12:U17. I do actually get the correct results with this following set up,
gt; but I just think I'm being lucky. I can get the set up work correct in
gt; the
gt; CountA function, but not the CountIF...
gt; =COUNTIF(U5:U10:U12:U17,quot;3.028quot;)/COUNTA(U5:U10,U12:U17)
gt; 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:
gt;One way
gt;
gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;} ),3.028))/COUNTA(U5:U10,U12:U17)
gt;
gt;or
gt;
gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;} ),3.028))/COUNT(U5:U10,U12:U17)
gt;
gt;since COUNT count numbers only while COUNTA count numbers and text
gt;
gt;gt; Hi all, I'm reading this site and am really impressed with the knowledge.
gt;gt; I'm
gt;[quoted text clipped - 8 lines]
gt;gt; =COUNTIF(U5:U10:U12:U17,quot;3.028quot;)/COUNTA(U5:U10,U12:U17)
gt;gt; 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:
gt;One way
gt;
gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;} ),3.028))/COUNTA(U5:U10,U12:U17)
gt;
gt;or
gt;
gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;} ),3.028))/COUNT(U5:U10,U12:U17)
gt;
gt;since COUNT count numbers only while COUNTA count numbers and text
gt;
gt;gt; Hi all, I'm reading this site and am really impressed with the knowledge.
gt;gt; I'm
gt;[quoted text clipped - 8 lines]
gt;gt; =COUNTIF(U5:U10:U12:U17,quot;3.028quot;)/COUNTA(U5:U10,U12:U17)
gt;gt; 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({quot;U5:U10quot;,quot;U12:U17quot;}),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

nwexcelsolutions.com

quot;Debbiequot; lt;u21726@uwegt; wrote in message news:600ddca90e555@uwe...
gt; Peo, thank you for the quick response, since I new to this may I ask what
gt; does INDIRECT mean? and based on what I've read isn't SUMPRODUCT a
gt; mutiplier
gt; function? Thank you again for your time on this.
gt;
gt; Peo Sjoblom wrote:
gt;gt;One way
gt;gt;
gt;gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot; }),3.028))/COUNTA(U5:U10,U12:U17)
gt;gt;
gt;gt;or
gt;gt;
gt;gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot; }),3.028))/COUNT(U5:U10,U12:U17)
gt;gt;
gt;gt;since COUNT count numbers only while COUNTA count numbers and text
gt;gt;
gt;gt;gt; Hi all, I'm reading this site and am really impressed with the
gt;gt;gt; knowledge.
gt;gt;gt; I'm
gt;gt;[quoted text clipped - 8 lines]
gt;gt;gt; =COUNTIF(U5:U10:U12:U17,quot;3.028quot;)/COUNTA(U5:U10,U12:U17)
gt;gt;gt; Thanks so much for your help.
It's because you aren't really using multiple ranges

=COUNTIF(U5:U10:U12:U17,quot;3.028quot;)

is the same as

=COUNTIF(U5:U17,quot;3.028quot;)

you can test that by putting 3.028 in U11 and notice that it will be
counted, but

=SUM(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;}),3.028) )

will not count 3.028 in U11

but if U11 is blank the result would be the same--

Regards,

Peo Sjoblom

nwexcelsolutions.comquot;Debbiequot; lt;u21726@uwegt; wrote in message news:600e02923e903@uwe...
gt; It's funny I get the same results no matter what formula I use the one you
gt; sent and mine, I just look at mine and it doesn't appear logical...
gt;
gt; Peo Sjoblom wrote:
gt;gt;One way
gt;gt;
gt;gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot; }),3.028))/COUNTA(U5:U10,U12:U17)
gt;gt;
gt;gt;or
gt;gt;
gt;gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot; }),3.028))/COUNT(U5:U10,U12:U17)
gt;gt;
gt;gt;since COUNT count numbers only while COUNTA count numbers and text
gt;gt;
gt;gt;gt; Hi all, I'm reading this site and am really impressed with the
gt;gt;gt; knowledge.
gt;gt;gt; I'm
gt;gt;[quoted text clipped - 8 lines]
gt;gt;gt; =COUNTIF(U5:U10:U12:U17,quot;3.028quot;)/COUNTA(U5:U10,U12:U17)
gt;gt;gt; Thanks so much for your help.
Peo Sjoblom wrote...
....
gt;or
gt;
gt;=SUMPRODUCT(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;} ),3.028))
gt;/COUNT(U5:U10,U12:U17)
gt;
gt;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:
gt;It's because you aren't really using multiple ranges
gt;
gt;=COUNTIF(U5:U10:U12:U17,quot;3.028quot;)
gt;
gt;is the same as
gt;
gt;=COUNTIF(U5:U17,quot;3.028quot;)
gt;
gt;you can test that by putting 3.028 in U11 and notice that it will be
gt;counted, but
gt;
gt;=SUM(COUNTIF(INDIRECT({quot;U5:U10quot;,quot;U12:U17quot;}),3.028 ))
gt;
gt;will not count 3.028 in U11
gt;
gt;but if U11 is blank the result would be the same
gt;
gt;gt; It's funny I get the same results no matter what formula I use the one you
gt;gt; sent and mine, I just look at mine and it doesn't appear logical...
gt;[quoted text clipped - 15 lines]
gt;gt;gt;gt; =COUNTIF(U5:U10:U12:U17,quot;3.028quot;)/COUNTA(U5:U10,U12:U17)
gt;gt;gt;gt; Thanks so much for your help.