# Average data from multiple non adjacent columns with criteria

#### mdexcel

##### New Member
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.

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))

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:

 CELL FORMULA Q3 =IFERROR(AVERAGEIFS(C3:C24,B3:B24,B3,C3:C24,"<>0"),0) R3 =IFERROR(AVERAGEIFS(F3:F24,B3:B24,B3,F3:F24,"<>0"),0) S3 =IFERROR(AVERAGEIFS(I3:I24,B3:B24,B3,I3:I24,"<>0"),0) T3 =AVERAGEIFS(Q3:S3,"<>0",Q3:S3)

<tbody>
</tbody>

Replies
4
Views
215
Replies
3
Views
237
Replies
1
Views
281
Replies
5
Views
314
Replies
5
Views
319

1,203,757
Messages
6,057,178
Members
444,911
Latest member
Uncommon1

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back