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

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

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

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)

"Chris" wrote:

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

Summing the same column from many sheets with specific Row IDnumbers
 

Chris wrote:
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")))

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

Hi

use the Consolidate function.
On a blanks sheet DataConsolidationuse the selection icon to select your
first rangeAddselect next rangeAdd
Use labels intop rowLeft columnOK

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

--
Regards
Roger Govier

"Chris" wrote in message
...
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

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

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.
--
Thanks,
Shane Devenshire


"Chris" wrote:

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

Summing the same column from many sheets with specific Row IDnumb
 

On Nov 7, 12:21*pm, ShaneDevenshire
wrote:
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.
--
Thanks,
Shane Devenshire

"Chris" wrote:
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

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

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

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(A ugust08!A$1:A$10,A1,Sept08!B$1:B$10)


--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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

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

Hi Chris

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

--
Regards
Roger Govier

"Chris" wrote in message
...
On Nov 7, 12:21 pm, ShaneDevenshire
wrote:
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.
--
Thanks,
Shane Devenshire

"Chris" wrote:
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

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

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

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(A ugust08!A$1:A$10,A1,August08!B$1:B$10)

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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(A ugust08!A$1:A$10,A1,Sept08!B$1:B$10)


--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
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

Summing the same column from many sheets with specific Row IDnumbers
 

On Nov 7, 1:00*pm, "T. Valko" wrote:
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(A ugust08!A$1:A$10,A1,August08!B$1:B$10)

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message

...

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(A ugust08!A$1:A$10,A1,Sept08!B$1:B$10)

--
Biff
Microsoft Excel MVP

"Chris" wrote in message
...
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

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

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

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
On Nov 7, 1:00 pm, "T. Valko" wrote:
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(A ugust08!A$1:A$10,A1,August08!B$1:B$10)

--
Biff
Microsoft Excel MVP

"T. Valko" wrote in message

...

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(A ugust08!A$1:A$10,A1,Sept08!B$1:B$10)

--
Biff
Microsoft Excel MVP

"Chris" wrote in message
...
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

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

Summing the same column from many sheets with specific Row ID
 

Hi Chris,

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

--
Thanks,
Shane Devenshire


"Chris" wrote:

On Nov 7, 12:21 pm, ShaneDevenshire
wrote:
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.
--
Thanks,
Shane Devenshire

"Chris" wrote:
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

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