jason061872

New Member
I could use some help with this formula:
=IFERROR(SUMPRODUCT(SUMIFS(INDIRECT("'"&\$A\$4:\$A\$22&"'!B123:K123"),INDIRECT("'"&\$A\$4:\$A\$22&"'!B4:K4"),B4,INDIRECT("'"&\$A\$4:\$A\$22&"'!B5:K5"),\$Q\$9))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&\$A\$4:\$A\$22&"'!B4:K4"),B4,INDIRECT("'"&\$A\$4:\$A\$22&"'!B5:K5"),\$Q\$9)),IF(N4=1,SUMPRODUCT(SUMIFS(INDIRECT("'"&\$A\$4:\$A\$22&"'!B123:K123"),INDIRECT("'"&\$A\$4:\$A\$22&"'!B4:K4"),B4,INDIRECT("'"&\$A\$4:\$A\$22&"'!B5:K5"),\$Q\$9))/1,""))

This formula is a multi-worksheet averaging of numbers given specific criteria.
A4:A22 refers to the names of the worksheets. In this case Week 1, Week 2, Week 3…, Week 19.
B123:K123 refers to the final line on each of the worksheets,which is an average of rows 7 – 122.
B4:K4 is a criteria reference; in this case it refers to a specific state.
B5:K5 is also a criteria reference; referring to a product type.
B4 refers to the specific state I want the formula to search for in B4:K4
Q9 refers to the specific product type I want the formula to search for in B5:K5.

The formula works fine in that it successfully searches through all the worksheets, finds the task and state match and averages the results. If there is only one instance of the state/task, it returns the one result. If there are no matches, it leaves the cell blank.

The problem with this formula is that if I need to add or remove a row above line 123, the B123:K123 reference does not automatically update to reflect the removal or addition of the new row. In order to save myself some time if I make a change, I would love to be able to find a way to make this formula update on its own.
Any help or assistance you could provide would be greatly appreciated!

Last edited:

Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

yky

Well-known Member
Excel doesn't change anything inside double quotation marks. Try this:

INDIRECT("'"&\$A\$4:\$A\$22&"'!B" & ROW(B123) & ":K" & ROW(K123))

Replies
5
Views
180
Replies
2
Views
371
Replies
3
Views
356
Replies
1
Views
317
Replies
1
Views
91

1,141,476
Messages
5,706,604
Members
421,460
Latest member
Taamrak

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.

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