Look up and sum across sheets?

[Simon Lloyd]

Hi all, i am trying to construct a formula to sum the values across worksheets for a given value on the source sheet but i am having a great deal of difficulty. Here's what i have, A1:A8 houses the sheet names.

=SUMPRODUCT(N(INDIRECT(A1:A8&"!" & A10)))*SUMPRODUCT(INDIRECT(A1:A8&"!" & B10:F10))

The above gives me a circular reference.

Ideally i wanted it as a Vlookup so if the value of A10 was found in a few of the sheets in different rows then sum total of those sheets B and found row : F and found row, for all the sheets the value of A10 appears on. Is this possible in formula?

[grichey]

Do you mean if the value in A10 is also in A10 of the other sheets?

The below confuses me. I don't understand what you're trying to do w/ these other columns.
"sum total of those sheets B and found row : F and found row, for all the sheets the value of A10 appears on."

[Simon Lloyd]

Any ideas on how to achieve the above using formula? preferrably using the vlookup kind of method to find the occurence anywhere in column A on all sheets and sum the range B & found row : F & found row for all the sheets?

[Bob Phillips]

I have never been able to get a formula that sums multiple columns in the few times I have tried, but a single column is okY

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1:A8"),A10,
INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!B1:B8")))

[Simon Lloyd]

Thanks for the reply Bob, i want to sum the row from B:F that the value (in this case A10) is found on for every sheet (it may be in column A on row 3 on sheet2 and row 15 on sheet3 etc.) so sum sheet2 B3:F3 + sheet3 B15:F15 etc, i guess its going to have to be a udf, i was really trying to stay away from VBA for this particular project.

[Bob Phillips]

You could do it with 5 SPs.

=SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1:A8"),A10,
INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!B1:B8")))
+SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1:A8"),A10,
INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!C1:C8")))
+SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1:A8"),A10,
INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!D1:d8")))
+SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1:A8"),A10,
INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!E1:E8")))
+SUMPRODUCT(SUMIF(INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!A1:A8"),A10,
INDIRECT("'Sheet"&ROW(INDIRECT("1:3"))&"'!F1:F8")))

But a UDF is better IMO, this technique is pretty dire at the best of times, never mind 5 of them.

[Simon Lloyd]

I agree Bob it's horrendous!, you can see the way i was attempting to go by locating the sheetnames in cells, and i thought that sumproduct/indirect was the way to go rather than Index/Match as i thought that would be horrendous too. I agree that a UDF is a lot neater but not my ideal solution.

Thanks for the time spent on this Bob!