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.