finding average - Urgent

[Vicky]

Please some one help me out in finding the average
Below we have 2 colums A and B, it looks like this
column A Column B
A012 35
A013 50
A014 25
A012 0
A013 0
A014 40
I want the average of the column B for the particular numbers A012, A013, A014

 

[Sheeloo]

Use this in C1
=SUMIF($A$1:$A$6,A1,$B$1:$B$6)/COUNTIF($A$1:$A$6,A1)
and copy down
This will give you the average for the corresponding value in Col A

 

[Vicky]

Oh! it is returning me the Zero value, i mean to say the column "B" has even
the zero value in that, but when we find the average it should ignore "zero"

Can you dig more into that

thanks for your time!!

Vicky

 

[Sheeloo]

Try
=SUMIF($A$1:$A$6,A1,$B$1:$B$6)/SUMPRODUCT(--($A$1:$A$6=A1),--($B$1:$B$6>0))

 

[Vicky]

Genius sheeloo, it worked

Thanks a lot!!!!!
Vicky