Try the below which will lookup the value in C1 in Sheet1,Sheet2 ColA and add
the corresponding values in ColB

=SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2"} &"'!A:A"),C1,
INDIRECT("'"& {"sheet1","sheet2"} &"'!B:B")))

'To try with more sheets Sheet1, Sheet2,Sheet3....Sheet25
=SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIREC T("1:25"))&
"'!A:A"),C1,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:2 5"))&"'!B:B")))

If this post helps click Yes
---------------
Jacob Skaria


"Friday" wrote:

Currently, I can use vlookup to find the value in a cell for any w'sheet.

Why can't I sum a series of vlookups? Each vlookup would scan a range of
sheets, then find the correct row (if it exists, if not skip this sheet), and
these values could be summed.

=SUM(FIRST:LAST!B3) Works, but does not allow any variation among rows.
To use this, I have to match the rows of a hundred sheets!!

=VLOOKUP(A6,FIRST:LAST!A4:D100,5,FALSE) Does not give an error, but does
not give a value either.

=VLOOKUP(A6,'*',A4:D100,5,FALSE) Same result.

Could I use something like =SUM(VLOOKUP(............) ?

I tried asking for advice and got some ideas that work IF I had a PH.D. in
Excel.

Isn't there a simple way to sum a value, in a range of sheets, where the
value appears in different rows in each sheet, but the same column?

IF COLUMN A CONTAINS A UNIQUE VALUE (ie: DATE or PART#) TO SEARCH.
IF THE COLUMNS IN EACH SHEET ARE THE SAME.

Right now I'm guessing this can only be done by very smart people who make
their living teaching Excel or writing books about Excel.



----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions