Using Sumif and Countif to find Average but Blank cells giving problems

[Winner_texas]

I have 2 rows like this:

<TABLE style="WIDTH: 108pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=144 border=0 x:str><COLGROUP><COL style="WIDTH: 54pt" span=2 width=72><TBODY><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: transparent" width=72 height=21>Month</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 54pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=72>Value</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffff99" height=21>A</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99" align=right x:num>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffff99" height=21>A</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffff99" height=21>A</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffff99" align=right x:num>6</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffcc99" height=21>B</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" align=right x:num>4</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffcc99" height=21>B</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ffcc99" height=21>B</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" align=right x:num>8</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ff99cc" height=21>C</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ff99cc"></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ccffcc" height=21>D</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc" align=right x:num>2</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.75pt; BACKGROUND-COLOR: #ccffcc" height=21>D</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccffcc" align=right x:num>10</TD></TR></TBODY></TABLE>

I need to find the avearge for A, B, C, D and the answers should be 4,6,0,6.

I am using sumif and countif but its not working.

 

[Domenic]

Assuming that A2:B10 contains the data, let D2 contain A, D3 contain B, D4 contain C, etc., then try...


E2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,AVERAGE(IF($A$2:$A$10=D2,IF(ISNUMBER($B$2:$B$10),$B$2:$B$10)))))

 

[Winner_texas]

Thanks, its working.

Is there anything simpler using Sumif and Countif Conditions? If not, I am happy with what you suggested.


Thanks a lot!

 

[SteveWright]

=IF(B3="","",SUMIF(A:A,A3,B:B)/(COUNTIF(A:A,A3)-COUNTIFS(B:B,"",A:A,A3)))

<TABLE style="WIDTH: 152px; BORDER-COLLAPSE: collapse; HEIGHT: 273px" cellSpacing=0 cellPadding=0 width=152 border=0><COLGROUP><COL style="WIDTH: 52pt" span=3 width=70><TBODY><TR style="HEIGHT: 15.9pt" height=21><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.9pt; BACKGROUND-COLOR: transparent" width=70 height=21>Col A</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=70>Col B</TD><TD class=xl79 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=70>Col C</TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15.9pt; BACKGROUND-COLOR: #efefef" width=70 height=21>Month

</TD><TD class=xl71 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #3867a6 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #efefef" width=70>Value</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl66 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ffff99" width=70 height=21>A</TD><TD class=xl72 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8 1pt solid; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #ffff99" width=70 u1:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4</TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl66 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ffff99" width=70 height=21>A</TD><TD class=xl73 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #ffff99" width=70> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl66 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ffff99" width=70 height=21>A</TD><TD class=xl72 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #ffff99" width=70 u1:num>6</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>4</TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl67 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ffcc99" width=70 height=21>B</TD><TD class=xl74 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #ffcc99" width=70 u1:num>4</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6</TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl67 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ffcc99" width=70 height=21>B</TD><TD class=xl75 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #ffcc99" width=70> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl67 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ffcc99" width=70 height=21>B</TD><TD class=xl74 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #ffcc99" width=70 u1:num>8</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6</TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl68 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ff99cc" width=70 height=21>C</TD><TD class=xl76 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #ff99cc" width=70> </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl69 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ccffcc" width=70 height=21>D</TD><TD class=xl77 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #ece9d8 1pt solid; BACKGROUND-COLOR: #ccffcc" width=70 u1:num>2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6</TD></TR><TR style="HEIGHT: 15.9pt" height=21><TD class=xl70 style="BORDER-RIGHT: #ece9d8 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #3867a6 1pt solid; WIDTH: 52pt; BORDER-BOTTOM: #3867a6 1pt solid; HEIGHT: 15.9pt; BACKGROUND-COLOR: #ccffcc" width=70 height=21>D</TD><TD class=xl78 style="BORDER-RIGHT: #3867a6 1pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 52pt; BORDER-BOTTOM: #3867a6 1pt solid; BACKGROUND-COLOR: #ccffcc" width=70 u1:num>10

</TD><TD id=td_post_2124504 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right>6</TD></TR></TBODY></TABLE>

Sorry the table above looks a bit wierd but the formula works!