Averageif across multiple sheets with variable content

Chantilly

New Member
Joined
Jul 15, 2019
Messages
10
Hi there,

I am trying to do an average of data on a summary sheet from multiple sheets, however, the data I need is coming from an already existing averageif formula on each sheet. I have set up the columns and rows to be exact on each sheet for the values needed. Each sheet will be labeled by their respective week, but also the date that starts the week, so I need to include the variable "*" after the week range so that it will include all the weeks. The data will also vary on each sheet, as every week is different.
With this, want to do one formula that will encompass the data that is separated by weeks for the year (so it is future dated to include upcoming weeks that have not yet happened in the year) - not sure if this is possible?

I will provide the below example to help understand my query:

-Sheet 1 (labeled "Week 1 - Jan 6")
-On this sheet, cells containing averageif rules from M1 - M10 (qualifying content) and N1 - N10 (values averaged)
Cell M1 would be "AA"
Cell M2 would be "AB"
Cell M3 would be "AC" etc....

Cell N1 would be (averageif value with data from table that includes criteria of AA) lets say for this example it's "12"
Cell N2 would be "16" (using same averageif criteria, but for AB)
Cell N3 would be #Div/0! (because AC code was not used that week and so does not have a value to average)

- Sheet 2 (labeled "Week 2 - Jan 13")
- Cells M1:M10 are exactly the same, but value returns in cells N1:N10 would be different according to that weeks codes and average numbers

Sheets would continue accordingly for the rest of the year until Week 52.

Averageif rule in each sheet pulls the data based on the code and then I would like an average over all the sheets for the year as I continue to input new weeks into the spreadsheet.

This is the formula I tried for the first value of data (AA code), but failed...it just tells me to use an apostrophe:
=averageif ('Week 1*':'Week 52*'!M1:N10,"AA",'Week 1*':'Week 52*'!N1:N10)

Am using "*" to include variable of dates following the week label of each sheet.
There are some #div/0 values on certain weeks where that code was not used, so I want to average all the ones that do have data.
As this is future dated, weeks that have not happened yet will also not have any data, but I don't want to have to change the formula each week to include the average summary total....not sure if this is possible??

I hope there is a way!!!!

PLEASE HELP!

Thanks in advance! :)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Hi,

Are you sure that you should be generating your multi-sheet average via taking an 'average of averages' (a dubious and risky measure, in general)?

This will only give correct results if the number of occurrences of each criteria ("AA", etc.) is precisely the same within all sheets. Otherwise, the average should be taken from the actual data, not the already generated averages.

For example, imagine your raw data for each of the first three sheets was this:

Week 1 - Jan 6

AG139
AJ540
AA728
AD15
AA294
AA854
AA565
AA371
AA595
AA919
AA489
AA163
AA613
AA150
AA372

<tbody>
</tbody>

Week 2 - Jan 13

AA693
AH180
AJ454
AA547
AA276
AA794
AA986
AA477
AE563
AH759
AB961
AA367
AC329
AA577
AC221

<tbody>
</tbody>

Week 3 - Jan 20

AI927
AA105
AJ85
AA741
AB796
AD272
AG127
AB819
AF186
AA3
AF15
AI851
AA106
AH841
AA117

<tbody>
</tbody>

The corresponding individual sheet averages for "AA" (i.e. your cell N1 values) would be:

Week 1 - Jan 6: 509.416666666667
Week 2 - Jan 13: 589.625
Week 3 - Jan 20: 214.4

Your current proposed set-up for the multi-sheet average would then take the average of these three values, i.e. 437.813888888889.

However, combining the data from the above three sheets would lead to a correct average for "AA" of 476.08.

Regards
 
Upvote 0
Xor Lx - Fair point
I would still need assistance with the syntax of this rule, however, to generate across multiple sheets that will include empty data, as well as, future data.

Any suggestions?
 
Upvote 0
The raw data for the range on each sheet is A10:A50
The criteria would be the individual codes, "AA" for this example.
The average_range on each sheet is J10:J50

Have tried using the following averageif with the raw data as above, and have also taken out the "*" variable to just include the sheets from Week 1 - Week 52, but still invalid - perhaps I'm missing apostrophes or something in the syntax?

AVERAGEIF("'Week 1 - Jan 6'!A10:A50':'Week 52 - Dec 29'!A10:A50","AA","'Week 1 - Jan 6'!J10:J50':'Week 52 - Dec 29'!J10:J50'")

TIA!!
 
Upvote 0
Happy to help, though the intention of my previous post wasn't just to debate the correct method of averaging. The relevant point is that there's a big difference in a formula which calculates over a single value from multiple sheets and one which calculates over multiple values from multiple sheets.

Bottom line is that I'd need to know the relevant ranges in each sheet in which the raw data is held.

It would also be useful to know if the sheet names all follow the same format as those you give.

Regards
 
Upvote 0
Apologies. Posted without seeing your previous. Thanks. Will get back to you with a solution soon.
 
Upvote 0
XOR LX - No worries...Understood and is a fair and valid point that I had not considered previously, so thank you for bringing it to my attention :)

As for the sheet names, yes, they all follow the same format of "Week 1 - Jan 6, Week 2 - Jan 13, Week 3 - Jan 20" etc.
 
Upvote 0
Try the following array formula**:

=SUM(IFERROR(SUMIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("06/01/2019"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA",INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("06/01/2019"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!J:J")),0))/SUM(IFERROR(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("06/01/2019"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA"),0))

Change the hard-coded reference to "AA" (in red) as desired (or, better still, put that value in an actual worksheet cell and reference that within the formula instead).

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
 
Upvote 0
First, I would like to thank you for taking the time to do this coding.
Unfortunately, it did not work :(
I needed to change the start date within the code to 12/30/2018, as the Week 1 started on the Sunday and needed to include Jan 1. And so, Week 1 was Dec 30
I also added "*" to the "AA" since AA has various other alphanumeric behind it within the worksheets and so requires this variable (ie. AA5C900, AA6C700, etc).
Not sure if these 2 changes caused it to not work, but are needed.

Please see adjusted code below based on this and let me know if I have messed something up! (PS. I did the Ctrl+Shft+Enter for the array when entering)

=SUM(IFERROR(SUMIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("30/12/2018"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA*",INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("30/12/2018"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!J:J")),0))/SUM(IFERROR(COUNTIF(INDIRECT("'Week "&ROW(INDIRECT("1:52"))&" - "&TEXT("30/12/2018"+7*(ROW(INDIRECT("1:52"))-1),"mmm d")&"'!A:A"),"AA*"),0))
 
Upvote 0
I needed to change the start date within the code to 12/30/2018

This makes me suspect you are using a US version of Excel, correct? If so, the dates in the formula I gave you need rewriting so that they are of the form mm/dd/yyyy, not dd/mm/yyyy.

Regards
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top