Multiple Indirects with named range

0
votes

I'm trying to write a formula using SUMPRODUCT, SUMIF, INDIRECT and a named range. My formula is: 

=SUMPRODUCT(SUMIF(INDIRECT("'"&INDIRECT("EightiesNames")&"'!G8:HI8"),$A$2,INDEX(INDIRECT("'"&INDIRECT("EightiesNames")&"'!G9:HI300"),MATCH($A$1,INDIRECT("'"&INDIRECT("EightiesNames")&"'!E9:E300"),0),0)))

The formula goes through every worksheet in the named range and does a lookup to both the columns (looking up $A$2 in G8:HI8. note that $A$2 may occur multiple times in one worksheet) and rows (looking up $A$1 to E9:E300) before summing together all the return values with SUMIF/SUMPRODUCT.

The formula was inspired by the below simplified version but with SUMPRODUCT and INDIRECT layered in to allow it to perform the same function across multiple tabs.

=SUMIF(G8:HI8, $A$2, INDEX(G9:HI300, MATCH($A$1, E9:E300,0), 0))

See below for a more detailed description of the workbook.

Worksheets:

Sheet1 - houses a named range in cells B2:B7 {Name1, Name2, Name3, Name4, Name5, Name6}. The named range is called "EightiesNames"

Name1 - where the formula is located. The formula references worksheets Name1, Name2, Name3, Name4, Name5, Name6, basically all the worksheets in the named range.

There are three occurrences of "INDIRECT("'"&INDIRECT("EightiesNames")" - after the SUMIF, one after INDEX and after the MATCH. The first occurrence of the two INDIRECTs, works the way I want it to (when I evaluate the formula). It expands the INDIRECT("EightiesNames") into an array {"Name1";"Name2";"Name3";"Name4";"Name5";"Name6"}. The formula then proceeds to tack on the range "'!G8:HI8" after each "Name" - "'Name1'!G8:HI8";...;"'Name6'!G8:HI8". However, for the INDIRECTs that occur after the INDEX and the MATCH, it does not return an array like the first one. On my computer, it returns "Name2" (returns "Name1" on my friend's), not sure why its different and why it returns those values instead of the entire array.

I'm not really sure how to fix this as the three INDIRECT formulas are identical but do not produce the same result. I also know INDIRECT formulas take up a lot of processing power so I'll gladly take alternatives. Any help/insight would be greatly appreciated! Thanks!

1
excelexcel-formulaformula

1 Answers

0
votes

While a single formula solution is possible, it would be very complex, inefficient, and not robust at all. For this reason, I would suggest that you use a regular SUMIF formula for each worksheet, where it would be placed in the same cell for each of your worksheets, let's say B1, and then you could simply use SUM to get your total, for example =SUM('Sheet1:Sheet5'!B1).

However, if you would like to try a single formula solution, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER.

Note that I have assumed that G8:HI8 and E9:E300 for each of your worksheets contain text values. If G8:HI8 contains numerical values, replace...

T(OFFSET(INDIRECT("'"&EightiesNames&"'!G8:HI8"),,COLUMN(INDIRECT("G8:HI8"))-7,,1))=$A$2

with

N(OFFSET(INDIRECT("'"&EightiesNames&"'!G8:HI8"),,COLUMN(INDIRECT("G8:HI8"))-7,,1))=$A$2

Also, if E9:E300 contains numerical values, replace...

T(OFFSET(INDIRECT("'"&TRANSPOSE(EightiesNames)&"'!E9:E300"),ROW(INDIRECT("9:300"))-9,0,1))=$A$1

with

N(OFFSET(INDIRECT("'"&TRANSPOSE(EightiesNames)&"'!E9:E300"),ROW(INDIRECT("9:300"))-9,0,1))=$A$1

Here's the formula...

=SUM(IF(T(OFFSET(INDIRECT("'"&EightiesNames&"'!G8:HI8"),,COLUMN(INDIRECT("G8:HI8"))-7,,1))=$A$2,N(OFFSET(INDIRECT("'"&EightiesNames&"'!G9:HI9"),TRANSPOSE(MMULT(TRANSPOSE(ROW(INDIRECT("9:300")))^0,IF(T(OFFSET(INDIRECT("'"&TRANSPOSE(EightiesNames)&"'!E9:E300"),ROW(INDIRECT("9:300"))-9,0,1))=$A$1,ROW(INDIRECT("9:300"))-9,0))),COLUMN(INDIRECT("G9:HI9"))-7,,1)),0))

Hope this helps!