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.

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.

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

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.

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.