# Averageifs across 2 sheets

#### Shygirl

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

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

Thank you soo much for your help Nikkie

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
can you put your working formula on the second sheet and then average the two results?

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

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

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

Last edited:
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...

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.

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
ABCDEFGHIJKLMN
2AverageCount
312357890012First sheet5.8758
4Second Sheet8.4285717
5
6True Ave7.066667
Sheet2
Cell Formulas
RangeFormula
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

Replies
0
Views
101
Replies
0
Views
255
Replies
3
Views
144
Replies
9
Views
671
Replies
9
Views
265

### Forum statistics

1,202,990
Messages
6,052,952
Members
444,621
Latest member
MIKOLAJ_R ### 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