Averageifs across 2 sheets

[Shygirl]

Hello,

I am trying to do an averageifs across 2 different sheets. So I have the following formula for the sheet and it works fine. But there is another sheet exactly like it but I would like to combine the two:

=IFERROR(AVERAGEIFS('Advisor Name 1'!$AA$5:$AA$71,'Advisor Name 1'!$H$5:$H$71,"Cas",'Advisor Name 1'!$Q$5:$Q$71,">="&$F$1,'Advisor Name 1'!$Q$5:$Q$71,"<="&$F$2),0)

- first part refers to the cells that need to be averaged
- second part is that another column must say "Cas"
- third and fourth part are for another criteria with particular dates (>= June 1 and <= June 30)

The Second sheets name is "Advisor Name 2" and the cells and everything is the same and also conditions.

I also tried this but it gives me an error:

=IFERROR(AVERAGEIFS('Advisor Name 1:Advisor Name 2'!$AA$5:$AA$71,'Advisor Name 1:Advisor Name 2'!$H$5:$H$71,"Cas",'Advisor Name 1:Advisor Name 2'!$Q$5:$Q$71,">="&$F$1,'Advisor Name 1:Advisor Name 2'!$Q$5:$Q$71,"<="&$F$2),0)

Thank you soo much for your help

Nikkie

 

[tybaltlives]

can you put your working formula on the second sheet and then average the two results?

 

[tybaltlives]

It would have to be a weighted average, of course. ( (average of sheet 1)*count of sheet 1 values+(average of sheet 2)*count of sheet 2 values)/(count of sheet 1 + count of sheet 2).

 

[Asala42]

Unless you know each sheet will have the same number of data points that will end up being averaged you won't want to do that. You'll likely need a weighted average.

I don't think Averagifs, Countifs, and Sumifs can span multiple sheets, so if it were me I'd break it out with this structure:
=(SUMIFS1+SUMIFS2)/(COUNTIFS1+COUNTIFS2)

(edit) - written before seeing the 2nd reply above

 

[Shygirl]

Hey guys thanks a lot!

So I did end up averaging the two results by calculating each sheet separately. However, my problem is that I am trying to average the two but without including zero's.

So I have this which works:

=AVERAGE(D8,E8)

After i do the calculations for the 2 sheets I get (Sheet 1 | Sheet 2 = Average)

ex. 6.3 | 5.0 = 5.7

But sometimes one or both of the column will have a zero:

ex. 0 | 5

and it gives me a 2.5 but I want it to be a 5 and not use the 0 if its there.

 

[Shygirl]

Also i now understand kind of what you mean but weighted average, but not sure how to do it, even with the instructions you guys gave. Help!

So what I used might now be incorrect - =AVERAGE(D8,E8)

Also, the zero issue...

 

[Shygirl]

Also i now understand kind of what you mean but weighted average, but not sure how to do it, even with the instructions you guys gave. Help!

So what I used might now be incorrect - =AVERAGE(D8,E8)

but my outputs on D8 are from the sheet 1 average and the output from sheet 2 averages are E8 then my total average would go F8. Next line would be D9 + E9 = total average F9 etc.

Also, the zero issue...

 

[Asala42]

You need to weight those averages with the number of data points that make up those averages.
Easiest way to do that is to have a second formula cell nearby with a COUNTIFS formula, using the same logical criteria as your AVERAGEIFs

To use an extreme example:
Sheet1 #points = 2000
Sheet1 Average = 100.0
Sheet1 SUMIF = 200000

Sheet2 #points = 10
Sheet2 Average = 80.0
Sheet2 SUMIF = 800

The average of that example is not measured by =AVERAGE(100,80) = 90. Sheet1 has 2000 points, and sheet2 only has 10. The real average will be much closer to Sheet1's average than Sheet2s, just based on the sheet difference in data points.

The actual average (weighted) would be:
=(2000*100+10*80)/(2000+10) = 99.9
or
=(AVERAGEIFS1*COUNTIFS1+AVERAGEIFS2*COUNTIFS2)/(COUNTIFS1+COUNTIFS2)


You could also consider using SUMIFS, this would simplify the number of terms.
=(200000+800)/(2000+10)
or
=(SUMIFS1+SUMIFS2)/(COUNTIFS1+COUNTIFS2)

In either case, your zero averages should fall off automatically if they are coming from the number of points = 0 for that sheet.

 

[tybaltlives]

I think Asala and I are saying the same thing. But here is a spreadsheet view. It accounts for the no zeros issue using sumif and countif. Again, you dont need to worry about weighted averages if you have an identical number of points (and zeroes) on both sheets. But also it isnt hard to put it into your equations.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
2													Average	Count
3	1	2	3	5	7	8	9	0	0	12		First sheet	5.875	8
4												Second Sheet	8.428571	7
5
6												True Ave	7.066667
Sheet2

Cell Formulas
Range	Formula
M3	=AVERAGEIF(A3:J3,"<>"&0,A3:J3)
M4	=Sheet3!M3
M6	=(M3*N3+M4*N4)/(N3+N4)
N3	=COUNTIF(A3:J3,"<>"&0)
N4	=Sheet3!N3