# Need to cal. avg of Col N (excl. blank cells) with only cells ref to Col. C as "Y"

1. ## Need to cal. avg of Col N (excl. blank cells) with only cells ref to Col. C as "Y"

Hi,

=SUMIF(\$C\$5:\$C\$39,"Y",N\$5:N\$39)/(COUNTIF(\$C\$5:\$C\$39,"Y")-COUNTBLANK(N\$5:N\$39))

In Col C, there are dropdowns for choosing Y or N. In Col N I only want to see the average of the numbers which say "Y" in Col. C. But the formula I have here counts all blank cells in Col. N including those don't say "Y" in Col. C.  Register To Reply

2. Hello Vinnie Chan:

This is an array formula; enter with the keys, Ctrl+Shift+Enter.
=AVERAGE(IF(\$C\$5:\$C\$39="Y",\$N\$5:\$N\$39))

Or

=SUMPRODUCT(--(\$C\$5:\$C\$39="Y"),(\$N\$5:\$N\$39))/SUMPRODUCT(--(\$C\$5:\$C\$39="Y"))  Register To Reply

3. Thanks Flintstone for you reply.

However these 2 formulas give the same result as the formula I have before. The answer is understated because it includes the blank cell in Column N which doesn't have a "Y" chosen in Column C.

I am now attaching a file. May be you will understand better what I really looking for. The formula I need in the row highlight in RED. The answer in
N43 should be 7.5.  Register To Reply

4. This should do it
=SUMPRODUCT(--(C5:C39="Y"),(N5:N39))/COUNTIF(C5:C39,"Y")
But it doesn't, it returns 4.5

your post said that you want the average of those entries that have a matching Y in column C, but your desired average is the average of the non-zero (column N) entries that have a Y in C.

If that is what you want
=SUMPRODUCT(--(C5:C39="Y"),(N5:N39))/SUMPRODUCT(--(C5:C39="Y"),--(N5:N39<>0))  Register To Reply

5. Thank you mikerickson.

This is exactly what I want. Works perfectly. I have never used SUMPRODUCT, I need to understand more about formulas.

You guys are awesome.

Thanks again.  Register To Reply