Average from multiple tabs

[mdobrien3117]

I’m trying to pull the average number for Mondays to put on a Q3 tab. It will pull from the July, Aug, and Sep tabs. But since there is nothing in the Aug or Sep tab yet I keep getting errors.
I got a formula that will work for each month but cant combine the 3 to make it work without errors. Here are the 3 I need to combine and find a way to disregard empty cells. How can I make this work?

=AVERAGE(AVERAGEIF(July!B3:B64,"Monday",July!V3:V64))
=AVERAGE(AVERAGEIF(August!B3:B64,"Monday",August!V3:V64))
=AVERAGE(AVERAGEIF(September!B3:B62,"Monday",September!V3:V62))

Any help would be very appreciated…

[amy_d2]

Try this..

=AVERAGE(ISSERROR(AVERAGEIF(July!B3:B64,"Monday",July!V3:V64)),ISERROR(AVERAGEIF(August!B3:B64,"Monday",August!V3:V64)),ISERROR((AVERAGEIF(September!B3:B62,"Monday",September!V3:V62))

if it doesn't work send some sample

Thank,s
A

[Tony Valko]

AVERAGEIF won't work with 3d references.

This will work...

List the sheet names in a range of cells:

A2 = July
A3 = August
A4 = September

B2 = Monday

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A2:A4&"'!B3:B64"),B2,INDIRECT("'"&A2:A4&"'!V3:V64")))/SUMPRODUCT(COUNTIF(INDIRECT("'"&A2:A4&"'!B3:B64"),B2))

[mdobrien3117]

Thanks guys but I still can’t get it to work. I think I’m not very good at explaining things, so I’ll just let you look at it.
Here is a copy of the part of the worksheet I’m working on. I just threw some generic numbers in it for now. As of now on the July page, the daily average for Monday should say 4,500. But I keep coming up with 643 on the Q3 page. It is dividing it by the total number of Mondays in the 3rd quarter. I just need it to show the average to date for that quarter.
It’s been breaking my brain for a few days now…

Copy of 2013 Tracking.xlsx

[Teethless mama]

Try it like this:

=(SUMIF(July!B$3:B$64,A3,July!V$3:V$64)+SUMIF(August!B$3:B$64,A3,August!V$3:V$64)+SUMIF(September!B$3:B$64,A3,September!V$3:V$64))/(COUNTIFS(July!B$3:B$64,A3,July!V$3:V$64,"<>")+COUNTIFS(August!B$3:B$64,A3,August!V$3:V$64,"<>")+COUNTIFS(September!B$3:B$64,A3,September!V$3:V$64,"<>"))

See the attached

[Tony Valko]

The problem with your formula is that the division denominator is not being calculated correctly.

On your Q3 sheet in A2:A9 drop the "s" from the weekdays.

Then, if you want to use the same basic formula that you're currently using:

=(SUMIF(July!B$3:B$64,A3,July!V$3:V$64)+SUMIF(August!B$3:B$64,A3,August!V$3:V$64)+SUMIF(September!B$3:B$62,A3,September!V$3:V$62))/(COUNTIFS(July!B$3:B$64,A3,July!V$3:V$64,"<>")+COUNTIFS(August!B$3:B$64,A3,August!V$3:V$64,"<>")+COUNTIFS(September!B$3:B$62,A3,September!V$3:V$62,"<>"))

Or, if you want to do it as I first suggested:

A13:A15 = the sheet names: July, August. September

Then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&A$13:A$15&"'!B3:B64"),A3,INDIRECT("'"&A$13:A$15&"'!V3:V64")))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&A$13:A$15&"'!B3:B64"),A3,INDIRECT("'"&A$13:A$15&"'!V3:V64"),"<>"))

[mdobrien3117]

Thank you so much Tony. That works perfectly. You are my new hero…

[Tony Valko]

You're welcome. Thanks for the feedback!