Average data from multiple non adjacent columns with criteria

[mdexcel]

Hi,
I am trying to build a quarterly report with avg numbers for each physician and avg for specialty. i wanted to use the avgs to compare to compare how the physician performance on these metrics comparing the the entire group with the same specialty. my problem is the average formula for the group is counting the zeros (if i use formula 2) I don't the zeros to be counted in the average.


I solved the problem for the physician average (column M,N,O) by using formula 1. but can't figure out for the columns Q,R,S.
Thank you in advance




https://i.stack.imgur.com/8lArP.png




Formula 1
=SUM(C3,F3,I3)/SUM(IF(C3=0,0,1),IF(F3=0,0,1),IF(I3=0,0,1))


Formula 2
=AVERAGEIF(iferror(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0),iferror(AVERAGEIFS(F3:F24,B3:B24,B3,F3:F24,"<>0"),0),iferror(AVERAGEIFS(I3:24,B3:B24,B3,I3:24,"<>0"),0))

 

[DanteAmor]

It would be like this

=SUM(IFERROR(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0),IFERROR(AVERAGEIFS(F3:F24,B3:B24,B3,F3:F24,"<>0"),0),IFERROR(AVERAGEIFS(I3:I24,B3:B24,B3,I3:I24,"<>0"),0))/SUM(IF(IFERROR(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0)=0,0,1),IF(IFERROR(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0),0,1),IF(IFERROR(AVERAGEIFS(I3:I24,B3:B24,B3,I3:I24,"<>0"),0)=0,0,1))


Or you could create 3 columns with the result of the average of C, F and I, and obtain the average of the 3 results, for example:

[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]CELL[/TD]
[TD]FORMULA[/TD]
[/TR]
[TR]
[TD]Q3[/TD]
[TD]=IFERROR(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0)[/TD]
[/TR]
[TR]
[TD]R3[/TD]
[TD]=IFERROR(AVERAGEIFS(F3:F24,B3:B24,B3,F3:F24,"<>0"),0)[/TD]
[/TR]
[TR]
[TD]S3[/TD]
[TD]=IFERROR(AVERAGEIFS(I3:I24,B3:B24,B3,I3:I24,"<>0"),0)[/TD]
[/TR]
[TR]
[TD]T3[/TD]
[TD]=AVERAGEIFS(Q3:S3,"<>0",Q3:S3)[/TD]
[/TR]
</tbody>[/TABLE]