SUMIF??

[Ket]

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)

 

[JE McGimpsey]

One way:

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

 

[Domenic]

Here are a few options...

Option One:

=SUMIF(Sheet1!A1:A100,16,Sheet1!I1:I100)+SUMIF(Sheet2!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:A100"),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!

 

[Ket]

Works great - thankyou!

One way:

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

 

[Ket]

Thanks for your reply Domenic

Here are a few options...

Option One:

=SUMIF(Sheet1!A1:A100,16,Sheet1!I1:I100)+SUMIF(Sheet2!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:A100"),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!