Summing the same column from many sheets with specific Row ID numbers

[Chris]

Hi all,

I've been working on this and can't seem to figure it out.

Here's my data set:

Sept08!
ID Measures
123 0
345 1
545 2
678 2
567 3

August08!
ID Measures
123 0
345 0
545 3
678 1
567 0

What I want to be able to do is Sum the Measures column in Sept08! and
August08!, on a different sheet so that it corresponds with each ID
number: So the result would look like

ID TotalMeasures
123 0
345 1
545 5
678 3
567 3

Any suggestions?

Thanks!

Chris

 

[Sheeloo]

Assuming August08 and Sep08 are sheet names
Enter this in B2 of the sheet having Ids in Col A
=sumproduct(--(August08!A1:A100=A1),(August08!B1:B100)) +
sumproduct(--(Sep08!A1:A100=A1),(Sep08!B1:B100))

Adjust 100 to the last row on each sheet (may be different)

If you have more sheets then you can have Sumproduct on each sheet in a
particular cell and then sum that cell across Sheets
=Sum(Sheet1:Sheet10!A1)

 

[Glenn]

Hi all,

I've been working on this and can't seem to figure it out.

Here's my data set:

Sept08!
ID Measures
123 0
345 1
545 2
678 2
567 3

August08!
ID Measures
123 0
345 0
545 3
678 1
567 0

What I want to be able to do is Sum the Measures column in Sept08! and
August08!, on a different sheet so that it corresponds with each ID
number: So the result would look like

ID TotalMeasures
123 0
345 1
545 5
678 3
567 3

Any suggestions?

Thanks!

Chris

Assuming your data is in A2:B6 on each sheet, put the sheet names in D12 of
your totals sheet and then put this in B2 of your totals sheet and copy down to B6:


=SUMPRODUCT(SUMIF(INDIRECT("'"&$D$1:$D$2&"'!A2:A6"),A2,INDIRECT("'"&$D$1:$D$2&"'!B2:B6")))

 

[Roger Govier]

Hi

use the Consolidate function.
On a blanks sheet Data>Consolidation>use the selection icon to select your
first range>Add>select next range>Add>
Use labels in>top row>Left column>OK

You can add more source ranges in the selection>Add routine.
When you run Consolidate again, your ranges will be remembered and the
result will reflect any changes to the source data.

 

[ShaneDevenshire]

Hi,

Since you show your data as being structurally identical if they are in the
same ranges on each sheet you can use a very simple approach:

=SUM(August08:September08!B2)

This formula can be copied down. This formula assumes that the entries for
say 123 are in B2 on each sheet.

If this helps, please click the Yes button.

 

[Chris]

Hi,

Since you show your data as being structurally identical if they are in the
same ranges on each sheet you can use a very simple approach:

=SUM(August08:September08!B2)

This formula can be copied down.  This formula assumes that the entriesfor
say 123 are in B2 on each sheet.

If this helps, please click the Yes button.

Hi Shane,

What if the ID numbers are not in the same cell on all sheets. For
example, ID number 123 is not in B2 on all sheets - it is on A12 on
August08, A36 on Sept08?

Thanks,
Chris

 

[T. Valko]

If you only have a "few" sheets...

List the unique codes in the range A1:An

Enter this formula in B1 and copy down as needed:

=SUMIF(Sept08!A$1:A$10,A1,Sept08!B$1:B$10)+SUMIF(August08!A$1:A$10,A1,Sept08!B$1:B$10)

 

[Roger Govier]

Hi Chris

With the Consolidate function solution I posted earlier, it does not matter
if the data is in the same order.

 

[T. Valko]

Ooops! Typo in the formula. That's what I get for Copy/pasting!

Correct formula should be:

=SUMIF(Sept08!A$1:A$10,A1,Sept08!B$1:B$10)+SUMIF(August08!A$1:A$10,A1,August08!B$1:B$10)

 

[Chris]

Ooops! Typo in the formula. That's what I get for Copy/pasting!

Correct formula should be:

=SUMIF(Sept08!A$1:A$10,A1,Sept08!B$1:B$10)+SUMIF(August08!A$1:A$10,A1,August08!B$1:B$10)

Thanks to all, I believe I got it to work out!

 

[T. Valko]

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Ooops! Typo in the formula. That's what I get for Copy/pasting!

Correct formula should be:

=SUMIF(Sept08!A$1:A$10,A1,Sept08!B$1:B$10)+SUMIF(August08!A$1:A$10,A1,August08!B$1:B$10)

Thanks to all, I believe I got it to work out!

 

[ShaneDevenshire]

Hi Chris,

My suggestion was for a limited situation, for more complex cases you
already had a solution - Data, Consolidate.