SUMIF across worksheets

[cjt_choccy]

I have a number sheets into which data is entered in table form but the tables may not be exactly the same size in each sheet. Each row has a reference number and I want to add up all values with the same reference and show the totals for each reference in a master table. Some sheets will have a value for a reference once whereas other sheets may hold for one reference a number of times. For example...

Master sheet
Column 1 Column 2 Column 3 etc etc
A001 £Total £Total £Total
A002 £Total £Total £Total
A003 £Total £Total £Total
etc
etc

Sheet 1
Column 1 Column 2 Column 3 etc etc
A001 £ £ £
A002 £ £ £
A001 £ £ £
A001 £ £ £
A003 £ £ £
etc
etc


Sheet 2 Column 1 Column 2 Column 3 etc etc
A003 £ £ £
A002 £ £ £
A001 £ £ £
A001 £ £ £
A002 £ £ £
etc
etc

[Søren Larsen]

If you could upload your workbook, stripped of sensitive information, it will be easier to provide you with the right help.

[cjt_choccy]

Thanks...it will take me a little time to strip out sensitive data but will supply workbook later

[Søren Larsen]

Ok, but before doing that, see if you can inmplement the following:

=SUMIF(Sheet2!$A$1:$A$10;$A1;Sheet2!B$1:B$10)+SUMIF(Sheet3!$A$1:$A$10;$A1;Sheet3!B$1:B$10) - you may have to change ; to ,

Assuming your reference numbers are in column A and the top-left data-entry is in B1 in your sheets. And then change the arrays to fit your data-tables.

And add a new =SUMIF() for each extra sheet.

[cjt_choccy]

Have amended the workbook and attached it...it is a simpler version than the actual but the principle id the same. The columns highlighted in purple are the references for whihc I need totals.

Thanks

[Søren Larsen]

See if the attachment is what you're looking for. If yes, then do a few checks to see if calculates correctly.

[cjt_choccy]

This is exactly what I was after...totals are all ok. I have already spent two hours trying to work this out so thanks very much.

Regards
CJ

[Søren Larsen]

I'm glad I could help. If you could please mark the thread as solved.