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

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.

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

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
---------------
Jacob Skaria


"ant_chaves" wrote:

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.

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

=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(INDIREC T("1:100"))
&"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1: 100"))&"'!B:B")))

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"ant_chaves" wrote:

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.

How LOOK UP and SUM the values from several spreadsheets in on
 

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" wrote:

=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(INDIREC T("1:100"))
&"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1: 100"))&"'!B:B")))

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"ant_chaves" wrote:

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.

How LOOK UP and SUM the values from several spreadsheets in on
 

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".

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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" wrote:

=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(INDIREC T("1:100"))
&"'!A:A"),C2,INDIRECT("'"&"sheet"&ROW(INDIRECT("1: 100"))&"'!B:B")))

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
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
---------------
Jacob Skaria


"ant_chaves" wrote:

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.