AVERAGEIFS with multiple worksheets

[a4turbo]

First I apologize if this has been posted before. I have searched the forum and google for many hours before resorting to post on this forum. If it has been posted before please point me in the right direction.

I am trying to do an "AVERAGEIFS" (not sure if that is what I should even be doing) involving multiple worksheets.

I currently have a working solution for a single worksheet:

=AVERAGEIFS(E5:E200,E5:E200,">0",N5:N200,"=Sale")

But now I need that same formula to take the average of multiple worksheets. Every worksheet has the same data in the same location.

The reason for this is so that I can put this formula on every worksheet and not have to flip back and forth to compare my total average vs. the current year I am on. I don't know if it matters or not but my worksheets are labeled by year (i.e. 2009, 2010, 2011).

Thanks.

[NBVC]

Try:

Please Login or Register  to view this content.

adjust the sheetname range 2009:2011 to actual sheet range.. then confirme the formula with CTRL+SHIFT+ENTER not just ENTER

or you can select 3 cells on each sheet and enter formula in 1:

Please Login or Register  to view this content.

and in the other

Please Login or Register  to view this content.

and in the 3rd:

Please Login or Register  to view this content.

where you entered the SUMIFS formula in B18 and the COUNTIFS in B19. This is the average of all sheets.

[a4turbo]

The first code worked like a charm. Thanks!

[dzimme06]

Hi - I have a question regarding the AVERAGEIFS function. I am currently using this formula =AVERAGEIFS('Historical Perf'!C$12:C$361,'Price to Book'!C$17:C$366,">C1",'Price to Book'!C$17:C$366,"<C2") in an attempt to average a column in sheet 'Historical Performance' if the values of a different column on a separate sheet (Price to Book) are in between two values (on the Price to Book sheet). If anyone could help me out I'd greatly appreciate it! Thanks in advance.

[arlu1201]

Dzimme06,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

[sumvas]