AverageIf across multiple pages

[therisingpet]

I have multiple identical Sheets named Day1 all the way through Day24.

I have cell B3 having Week 1, Week 2, Week 3, and Week 4

I am trying to get the averages of the cell C5 across all worksheets when B3 equals “Week 1”


I would need this for all weeks but can adjust the formula once I get it for Week 1.

Please help me try to figure this out

 

[Ezguy4u]

theris let's get the ball rolling. Here is my solution based on what I think is your problem. Now since we don't have your data input and output, I hope I guessed correctly. If you could download that XL2BB app and show us what you want, that would be a big help. So usually, I create more questions than answers, but we have to start somewhere.

Book1
	A	B	C
1
2
3		Week 1
4
5		25	20
Day 1

Cell Formulas
Range	Formula
B5	B5	=IF(B3="Week 1",AVERAGE('Day 1:Day 3'!C5),"")

 

[Domenic]

The following formula will average the amounts from B3 in sheets Day1 to Day24, where the corresponding value in C5 is "Week 1". Note that if the amounts contain negative numbers, replace ">=0" with ">=-9.99999999999999E+307.

=SUMPRODUCT(SUMIF(INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!B3"),"Week 1",INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!C5")))/SUMPRODUCT(COUNTIFS(INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!B3"),"Week 1",INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!C5"),">=0"))

Actually, since you're using M365...

=LET(weeks,INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!B3"),amounts,INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!C5"),SUMPRODUCT(SUMIF(weeks,"Week 1",amounts))/SUMPRODUCT(COUNTIFS(weeks,"Week 1",amounts,">=0")))

Hope this helps!

 

[therisingpet]

The following formula will average the amounts from B3 in sheets Day1 to Day24, where the corresponding value in C5 is "Week 1". Note that if the amounts contain negative numbers, replace ">=0" with ">=-9.99999999999999E+307.

=SUMPRODUCT(SUMIF(INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!B3"),"Week 1",INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!C5")))/SUMPRODUCT(COUNTIFS(INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!B3"),"Week 1",INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!C5"),">=0"))

Actually, since you're using M365...

=LET(weeks,INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!B3"),amounts,INDIRECT("'Day"&SEQUENCE(24,,1,1)&"'!C5"),SUMPRODUCT(SUMIF(weeks,"Week 1",amounts))/SUMPRODUCT(COUNTIFS(weeks,"Week 1",amounts,">=0")))

Hope this helps!

This does help a lot. This formula works. I plan on changing the “Day1-Day24” to certain dates when I add data to the sheets. For example Day 1 might change to “03JUL23” and Day 2 might be “05Jul23” is there any way to add to the formula keep it even if I change the sheets name?

 

[Domenic]

How about if you use ether a Table (Ribbon >> Insert tab>> Tables group >> Table) or dynamic named range to list any and all sheets to be included in the average? So, basically, the formula would refer to the Table or dynamic named range for the list of sheet names. Then, as you add, remove, or replace sheet names, the formula would automatically adjust. For example, let's say we have the following Table, which is named Table1...

Book1
	A	B	C
1		Sheet List
2		Day 1
3		Day 2
4		Day 3
5		etc…
6
Sheet1

Now, we'll amend the formula so that it refers to the table for the list of sheet names, as follows...

=SUMPRODUCT(SUMIF(INDIRECT("'"&Table1[Sheet List]&"'!B3"),"Week 1",INDIRECT("'"&Table1[Sheet List]&"'!C5")))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&Table1[Sheet List]&"'!B3"),"Week 1",INDIRECT("'"&Table1[Sheet List]&"'!C5"),">=0"))

Hope this helps!

 

[Domenic]

Actually, I should have mentioned that when entering your sheet names in the Table in the format 03JUL23, you'll need to make sure that the cell(s) are formatted as text. Otherwise, Excel will recognize it as a true date. As a result, it won't match the date on the sheet tabs.

 

[therisingpet]

Actually, I should have mentioned that when entering your sheet names in the Table in the format 03JUL23, you'll need to make sure that the cell(s) are formatted as text. Otherwise, Excel will recognize it as a true date. As a result, it won't match the date on the sheet tabs.

Actually, I should have mentioned that when entering your sheet names in the Table in the format 03JUL23, you'll need to make sure that the cell(s) are formatted as text. Otherwise, Excel will recognize it as a true date. As a result, it won't match the date on the sheet tabs.

So, if I do this table, would I have to change the sheet names, and the names on the Table. Or is there a way that it can be done without having to adjust the table list as well?

 

[Domenic]

Yes, that's right. Whenever you change the sheet names for the relevant sheets, you'll have to change the sheet names listed in your table. However, this can be automated somewhat using VBA. For example, you can place a button on your worksheet to automatically update the sheet list in your table. So whenever the sheet names for your sheets are changed, you would click on the button, and the table containing the list of sheet names is automatically updated. Would this work for you?