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

C

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
 
S

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)
 
G

Glenn

Chris said:
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 D1:D2 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")))
 
R

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

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

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

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)
 
R

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

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)
 
C

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

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!
 
S

ShaneDevenshire

Hi Chris,

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top