Hello,

I have a workbook containing 3 sheets.
On sheet 1 in column A, I have reference numbers from 1 to 20. The
totals for these rows are in column I.
On sheet 2 in column A, I have reference numbers from 1 to 20. The
totals for these rows are on column L.
Sheet 3 is a summary sheet.
I need to do the following. Look for all occurances of ref 16 in sheet
1 and add up the corresponding figures in column I, then look for all
occurances of 16 in sheet 2 and add up the corresponding figures in
Column L to give me an overall sum total of all occurances of 16 in
both sheets.

I hope this is clear. Thanks in advance for any help.

Ket
London (UK)

One way:

=SUMIF(Sheet1!A:A,16,Sheet1!I:I) + SUMIF(Sheet2!A:A,16,Sheet2!L:L)

In article ,
Ket wrote:

Hello,

I have a workbook containing 3 sheets.
On sheet 1 in column A, I have reference numbers from 1 to 20. The
totals for these rows are in column I.
On sheet 2 in column A, I have reference numbers from 1 to 20. The
totals for these rows are on column L.
Sheet 3 is a summary sheet.
I need to do the following. Look for all occurances of ref 16 in sheet
1 and add up the corresponding figures in column I, then look for all
occurances of 16 in sheet 2 and add up the corresponding figures in
Column L to give me an overall sum total of all occurances of 16 in
both sheets.

I hope this is clear. Thanks in advance for any help.

Ket
London (UK)

Here are a few options...

Option One:

=SUMIF(Sheet1!A1:A100,16,Sheet1!I1:I100)+SUMIF(She et2!A1:A100,16,Sheet2!L
1:L100)

Option Two:

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{1,2}&"!A1:A100 "),16,OFFSET(INDIRECT("
Sheet"&{1,2}&"!I1:I100"),0,{0,3})))

or

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$1:$B$2&"'!A1:A10 0"),16,OFFSET(INDIRECT(
"'"&$B$1:$B$2&"'!I1:I100"),0,{0;3})))

....where B1:B2 contains the sheet names, such as Sheet1 and Sheet2.
Adjust the ranges accordingly.

Hope this helps!

In article ,
Ket wrote:

Hello,

I have a workbook containing 3 sheets.
On sheet 1 in column A, I have reference numbers from 1 to 20. The
totals for these rows are in column I.
On sheet 2 in column A, I have reference numbers from 1 to 20. The
totals for these rows are on column L.
Sheet 3 is a summary sheet.
I need to do the following. Look for all occurances of ref 16 in sheet
1 and add up the corresponding figures in column I, then look for all
occurances of 16 in sheet 2 and add up the corresponding figures in
Column L to give me an overall sum total of all occurances of 16 in
both sheets.

I hope this is clear. Thanks in advance for any help.

Ket
London (UK)

Works great - thankyou!

On Tue, 24 Jan 2006 07:31:16 -0700, JE McGimpsey
wrote:

One way:

=SUMIF(Sheet1!A:A,16,Sheet1!I:I) + SUMIF(Sheet2!A:A,16,Sheet2!L:L)

In article ,
Ket wrote:

Hello,

I have a workbook containing 3 sheets.
On sheet 1 in column A, I have reference numbers from 1 to 20. The
totals for these rows are in column I.
On sheet 2 in column A, I have reference numbers from 1 to 20. The
totals for these rows are on column L.
Sheet 3 is a summary sheet.
I need to do the following. Look for all occurances of ref 16 in sheet
1 and add up the corresponding figures in column I, then look for all
occurances of 16 in sheet 2 and add up the corresponding figures in
Column L to give me an overall sum total of all occurances of 16 in
both sheets.

I hope this is clear. Thanks in advance for any help.

Ket
London (UK)

Thanks for your reply Domenic

On Tue, 24 Jan 2006 09:44:05 -0500, Domenic
wrote:

Here are a few options...

Option One:

=SUMIF(Sheet1!A1:A100,16,Sheet1!I1:I100)+SUMIF(Sh eet2!A1:A100,16,Sheet2!L
1:L100)

Option Two:

=SUMPRODUCT(SUMIF(INDIRECT("Sheet"&{1,2}&"!A1:A10 0"),16,OFFSET(INDIRECT("
Sheet"&{1,2}&"!I1:I100"),0,{0,3})))

or

=SUMPRODUCT(SUMIF(INDIRECT("'"&$B$1:$B$2&"'!A1:A1 00"),16,OFFSET(INDIRECT(
"'"&$B$1:$B$2&"'!I1:I100"),0,{0;3})))

...where B1:B2 contains the sheet names, such as Sheet1 and Sheet2.
Adjust the ranges accordingly.

Hope this helps!

In article ,
Ket wrote:

Hello,

I have a workbook containing 3 sheets.
On sheet 1 in column A, I have reference numbers from 1 to 20. The
totals for these rows are in column I.
On sheet 2 in column A, I have reference numbers from 1 to 20. The
totals for these rows are on column L.
Sheet 3 is a summary sheet.
I need to do the following. Look for all occurances of ref 16 in sheet
1 and add up the corresponding figures in column I, then look for all
occurances of 16 in sheet 2 and add up the corresponding figures in
Column L to give me an overall sum total of all occurances of 16 in
both sheets.

I hope this is clear. Thanks in advance for any help.

Ket
London (UK)