| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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"))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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! |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Row numbers containing specific value within a column | Excel Worksheet Functions | |||
| summing numbers based on 2nd column | Excel Worksheet Functions | |||
| Adding numbers from specific cells in a number of sheets | Excel Worksheet Functions | |||
| Summing a column of Numbers | Excel Worksheet Functions | |||
| counting only specific numbers in column | Excel Worksheet Functions | |||
Linear Mode
