Lookup and sum using offset in multiple sheet

[sadath]

Hi
i use the formula to lookup and sum from a particular sheet

=SUM(OFFSET(INDIRECT(ADDRESS(MATCH($A6,KSA_ALL!$A:$A,0),1,,,"KSA_ALL")),0,9,1,2))

where 9 & 2 (OFFSET col & OFFSET width are is linked to a cell value

I changed the formula to get result from multiple sheets which returns error

=SUM(OFFSET(INDIRECT(ADDRESS(MATCH($A5,INDIRECT(SHNAME&"!$A:$A"),0),1,,,SHNAME)),0,9,1,2))

SHNAME has Sheet Names

even tried with vlookup CSE formula
{=SUM(VLOOKUP(A6,INDIRECT(ShName&"!A:ZI00"),ROW(9:10),0))}

any idea?

 

[Eric W]

First of all, I think your original formula is a bit over-engineered. You could use:

=SUM(INDEX(KSA_ALL!J:K,MATCH($A6,KSA_ALL!$A:$A,0),0))

or even

=SUM(VLOOKUP($A6,KSA_ALL!A:K,{10;11}))
with CSE

and get the same result.

As far as getting results from multiple pages, that's a tougher nut to crack. In general, array functions don't play nice with INDIRECT or OFFSET. There are a few workarounds, but nothing that always works. The best I came up with is a formula that sums values from multiple sheets, IF the values are in the same row on each sheet. So if you value in $A6 is "MyName" and "MyName" is found on row 10 of KSA_ALL, and row 10 of KSA_1, and row 10 of KSA_2, etc. then you're OK. If "MyName" is found on row 11 of KSA_3, then this won't work. But try:

=SUM(SUBTOTAL(9,INDIRECT(SHNAME&"!J" & MATCH($A6,KSA_ALL!A:A,0) & ":K" & MATCH($A6,KSA_ALL!A:A,0))))
with CSE.

(Or: =SUMPRODUCT(SUBTOTAL(9,INDIRECT(SHNAME&"!J" & MATCH($A6,KSA_ALL!A:A,0) & ":K" & MATCH($A6,KSA_ALL!A:A,0)))) without CSE.)

Hope this helps.