How LOOK UP and SUM the values from several spreadsheets in one.

[ant_chaves]

I have 3 Spreadsheets with lots of itens and some of them diferent between
them. I need to look up and resume in one, the sum of the values for the some
item.

 

[Jacob Skaria]

The below will sum the item orange present in Column A of
sheet1,sheet2,Sheet3 ; the values of which are present in Column B. Try and
feedback

In cell C2 = "orange"

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

PS: The above formula can further be shortened..if the sheetnames can be
typed in to a cell range..Or if the sheetnames are named in a sequencial way.

If this post helps click Yes

 

[T. Valko]

=SUMPRODUCT(SUMIF(INDIRECT("'"& {"sheet1","sheet2","Sheet3"}

&"'!A:A"),C2,INDIRECT("'"& {"sheet1","sheet2","Sheet3"} &"'!B:B")))
The above formula can further be shortened..
if the sheetnames are named in a sequencial way.

Sheet1, Sheet2, Sheet3 fall into the sequential naming convention.

=SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&{1,2,3}&"'!A:A"),C2,INDIRECT("'"&"sheet"&{1,2,3}&"'!B:B")))

If you had 100 sheets you wouldn't want to do this:

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15....100}

=SUMPRODUCT(SUMIF(INDIRECT("'"&"sheet"&ROW(INDIRECT("1:100"))
&"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1:100"))&"'!B:B")))

 

[Jacob Skaria]

Thanks Biff. Dont you think it is a easier to make some understand this way
first. Especially to someone new and then go with the refined/shorter
version...

 

[T. Valko]

Dont you think it is a easier to make some understand this way

I don't think the average user would understand either version without a
detailed explanation of how it works and what all the syntax means.

Very few responders include an explanation unless asked to do so.
Personally, I love to explain how things work. Just do a search on my name
and the string "exp101".