| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SOS
, spread acrossHi Folks,
Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SOS
Assuming the codes are on Sheet1 in A1:E10, the amounts you want to sum are
on Sheet1 F1:F10 and the codes you are looking for are on Sheet2 A1:E1, try entering this in Sheet2 F1: =SUMPRODUCT(--(Sheet1!A1:A10&Sheet1!B1:B10&Sheet1!C1:C10&Sheet1! D1:D10&Sheet1!E1:E10=A1&B1&C1&D1&E1),Sheet1!F1:F10 ) Change ranges and sheet names as needed, but unless you have XL2007, you cannot use entire columns (ie A:A or A1:A65536). "Raj" wrote: , spread acrossHi Folks, Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SOS
Or maybe
=SUMPRODUCT(--(Sheet1!A1:E10=A4:E4)*(Sheet1!F1:F10))/5 or =SUMPRODUCT(--(Sheet1!A1:E10=TEXT(A4:E4,"00"))*(Sheet1!F1:F10))/5 depending on how your number codes are formatted (numeric or text). I assumed the codes on your source sheet are text format. "JMB" wrote: Assuming the codes are on Sheet1 in A1:E10, the amounts you want to sum are on Sheet1 F1:F10 and the codes you are looking for are on Sheet2 A1:E1, try entering this in Sheet2 F1: =SUMPRODUCT(--(Sheet1!A1:A10&Sheet1!B1:B10&Sheet1!C1:C10&Sheet1! D1:D10&Sheet1!E1:E10=A1&B1&C1&D1&E1),Sheet1!F1:F10 ) Change ranges and sheet names as needed, but unless you have XL2007, you cannot use entire columns (ie A:A or A1:A65536). "Raj" wrote: , spread acrossHi Folks, Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SOS
Ignore that last suggestion - I wasn't thinking clearly.
"JMB" wrote: Or maybe =SUMPRODUCT(--(Sheet1!A1:E10=A4:E4)*(Sheet1!F1:F10))/5 or =SUMPRODUCT(--(Sheet1!A1:E10=TEXT(A4:E4,"00"))*(Sheet1!F1:F10))/5 depending on how your number codes are formatted (numeric or text). I assumed the codes on your source sheet are text format. "JMB" wrote: Assuming the codes are on Sheet1 in A1:E10, the amounts you want to sum are on Sheet1 F1:F10 and the codes you are looking for are on Sheet2 A1:E1, try entering this in Sheet2 F1: =SUMPRODUCT(--(Sheet1!A1:A10&Sheet1!B1:B10&Sheet1!C1:C10&Sheet1! D1:D10&Sheet1!E1:E10=A1&B1&C1&D1&E1),Sheet1!F1:F10 ) Change ranges and sheet names as needed, but unless you have XL2007, you cannot use entire columns (ie A:A or A1:A65536). "Raj" wrote: , spread acrossHi Folks, Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SOS
Hi JMB,
Thank you for your prompt response. I did try out that. I didn't explain correctly - apologies mate. The 5 sets of codes of a line item in Sheet 1 need to be compared from many rows in Sheet 2, and sum up all the rows in sheet 2 where ALL 5 cells in sheet 1 exactly match! I really got myself into a pickle here mate. Any help is greatly appreciated. Please see Both the tables: SHEET 1 WBS Code - Cost Report 01 03 01 01 00 00 01 03 01 02 00 00 01 03 01 03 00 00 01 03 01 04 00 00 01 03 01 05 00 00 01 03 01 06 00 00 01 03 01 07 00 00 01 03 01 08 00 00 SHEET 2 WBS Code - Change Order Log 01 03 07 04 00 11 01 03 07 04 00 10 01 03 07 04 00 09 01 03 07 04 00 06 01 03 07 04 00 12 01 03 07 04 30 00 01 01 02 03 00 00 01 03 01 07 00 00 01 03 01 08 00 00 As you see, one row from 1 row of Sheet 1 need to be compared against many in Sheet 2, and sum up all the $ amount from Sheet 2 (L19:L65), and return the sum to each of the line item in sheet 1 where all 6 cells match. Thanks again mate. Cheers, Raj "JMB" wrote: Assuming the codes are on Sheet1 in A1:E10, the amounts you want to sum are on Sheet1 F1:F10 and the codes you are looking for are on Sheet2 A1:E1, try entering this in Sheet2 F1: =SUMPRODUCT(--(Sheet1!A1:A10&Sheet1!B1:B10&Sheet1!C1:C10&Sheet1! D1:D10&Sheet1!E1:E10=A1&B1&C1&D1&E1),Sheet1!F1:F10 ) Change ranges and sheet names as needed, but unless you have XL2007, you cannot use entire columns (ie A:A or A1:A65536). "Raj" wrote: , spread acrossHi Folks, Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SOS
With this set up...
On a sheet named CostSheet, this configuration in A1:F4 Ent Div Dept Bin Item Total aa aa aa aa aa (blank) bb cc dd ee ff (blank) cc dd ee ff gg (blank) And, on a sheet named ChangeSheet, this configuration in A1:F5 Ent Div Dept Bin Item Amount aa aa aa aa aa 10 bb cc dd ee ff 100 cc dd ee ff gg 1000 aa aa aa aa aa 10 Try this On the ChangeSheet create a helper column G1: REF G2: =A2&B2&C2&D2&E2 Copy that formula down as far as you need. (The formula concatenates the account codes) (In my example, G2 returns "aaabaaabaa") Now...on the CostSheet F2: =SUMIF(ChangeSheet!$G$1:$G$20,CostSheet!A2&CostShe et!B2&CostSheet!C2&CostSheet!D2&CostSheet!E2,Chang eSheet!$F$1:$F$20) Copy that formula down as far as you need In my example, F2 returns 20, because 2 rows contain the aaabaaabaa account codes, at $10 each. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Raj" wrote: , spread acrossHi Folks, Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SOS
One thing you may want to check is are the codes on Sheet1 and Sheet2 numeric
or text??? Use the Isnumber and/or Istext functions to find out if you are not sure. I am assuming the codes are text, but they could be numbers that are formatted to show the leading 0. Assuming that the codes on the change order log and the codes on Sheet1 (cost report) are the same format (either text or numeric, but not mixed) and the data on Sheet2 (change order log) is in G19:L65, the codes you are looking for are in A1:E1, this worked on my machine: =SUMPRODUCT(--(Sheet2!G19:G65=A1),--(Sheet2!H19:H65=B1),--(Sheet2!I19:I65=C1),--(Sheet2!J19:J65=D1),--(Sheet2!K19:K65=E1),Sheet2!L19:L65) Another (somewhat shorter) suggestion that also seemed to work: =SUMPRODUCT(--(MMULT((--(Sheet2!G19:K65=A1:E1)),{1;1;1;1;1})=5),Sheet2!L19 :L65) "Raj" wrote: Hi JMB, Thank you for your prompt response. I did try out that. I didn't explain correctly - apologies mate. The 5 sets of codes of a line item in Sheet 1 need to be compared from many rows in Sheet 2, and sum up all the rows in sheet 2 where ALL 5 cells in sheet 1 exactly match! I really got myself into a pickle here mate. Any help is greatly appreciated. Please see Both the tables: SHEET 1 WBS Code - Cost Report 01 03 01 01 00 00 01 03 01 02 00 00 01 03 01 03 00 00 01 03 01 04 00 00 01 03 01 05 00 00 01 03 01 06 00 00 01 03 01 07 00 00 01 03 01 08 00 00 SHEET 2 WBS Code - Change Order Log 01 03 07 04 00 11 01 03 07 04 00 10 01 03 07 04 00 09 01 03 07 04 00 06 01 03 07 04 00 12 01 03 07 04 30 00 01 01 02 03 00 00 01 03 01 07 00 00 01 03 01 08 00 00 As you see, one row from 1 row of Sheet 1 need to be compared against many in Sheet 2, and sum up all the $ amount from Sheet 2 (L19:L65), and return the sum to each of the line item in sheet 1 where all 6 cells match. Thanks again mate. Cheers, Raj "JMB" wrote: Assuming the codes are on Sheet1 in A1:E10, the amounts you want to sum are on Sheet1 F1:F10 and the codes you are looking for are on Sheet2 A1:E1, try entering this in Sheet2 F1: =SUMPRODUCT(--(Sheet1!A1:A10&Sheet1!B1:B10&Sheet1!C1:C10&Sheet1! D1:D10&Sheet1!E1:E10=A1&B1&C1&D1&E1),Sheet1!F1:F10 ) Change ranges and sheet names as needed, but unless you have XL2007, you cannot use entire columns (ie A:A or A1:A65536). "Raj" wrote: , spread acrossHi Folks, Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SOS
Hi Ron,
You saved my life mate. I had this due tomorrow, and I was about to ask for an extension. Thanks again. Cheers, Raj "Ron Coderre" wrote: With this set up... On a sheet named CostSheet, this configuration in A1:F4 Ent Div Dept Bin Item Total aa aa aa aa aa (blank) bb cc dd ee ff (blank) cc dd ee ff gg (blank) And, on a sheet named ChangeSheet, this configuration in A1:F5 Ent Div Dept Bin Item Amount aa aa aa aa aa 10 bb cc dd ee ff 100 cc dd ee ff gg 1000 aa aa aa aa aa 10 Try this On the ChangeSheet create a helper column G1: REF G2: =A2&B2&C2&D2&E2 Copy that formula down as far as you need. (The formula concatenates the account codes) (In my example, G2 returns "aaabaaabaa") Now...on the CostSheet F2: =SUMIF(ChangeSheet!$G$1:$G$20,CostSheet!A2&CostShe et!B2&CostSheet!C2&CostSheet!D2&CostSheet!E2,Chang eSheet!$F$1:$F$20) Copy that formula down as far as you need In my example, F2 returns 20, because 2 rows contain the aaabaaabaa account codes, at $10 each. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Raj" wrote: , spread acrossHi Folks, Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SOS
I'm so glad i could help....and thanks for the feedback.
*********** Regards, Ron XL2002, WinXP "Raj" wrote: Hi Ron, You saved my life mate. I had this due tomorrow, and I was about to ask for an extension. Thanks again. Cheers, Raj "Ron Coderre" wrote: With this set up... On a sheet named CostSheet, this configuration in A1:F4 Ent Div Dept Bin Item Total aa aa aa aa aa (blank) bb cc dd ee ff (blank) cc dd ee ff gg (blank) And, on a sheet named ChangeSheet, this configuration in A1:F5 Ent Div Dept Bin Item Amount aa aa aa aa aa 10 bb cc dd ee ff 100 cc dd ee ff gg 1000 aa aa aa aa aa 10 Try this On the ChangeSheet create a helper column G1: REF G2: =A2&B2&C2&D2&E2 Copy that formula down as far as you need. (The formula concatenates the account codes) (In my example, G2 returns "aaabaaabaa") Now...on the CostSheet F2: =SUMIF(ChangeSheet!$G$1:$G$20,CostSheet!A2&CostShe et!B2&CostSheet!C2&CostSheet!D2&CostSheet!E2,Chang eSheet!$F$1:$F$20) Copy that formula down as far as you need In my example, F2 returns 20, because 2 rows contain the aaabaaabaa account codes, at $10 each. Is that something you can work with? *********** Regards, Ron XL2002, WinXP "Raj" wrote: , spread acrossHi Folks, Am new to this forum. I have a problem with comparing b/w 5 sets of cells on two different worksheets and pulling in the sume. For e.g., any line item in this cost report has a unique 10 character ID (2 characters each spread across 5 cells). This is the Cost account for a particular line item. I have about 200 line items in this cost report. Now this sheet needs to be linked to another worksheet (Change order log) to bring in total change orders against one particular line item. So the task at hand for me is to compare b/w 5 cells in one sheet to the corresponding 5 cells in another sheet, and return the sum of all the rows where the 5 cells match exactly. Hope some of the experts here can drop a line. Any help is appreciated. Thank you in advance. Raj. P.S. Please see the typical columns to compare. WBS Code 01 03 01 01 00 01 03 01 02 00 01 03 01 03 00 01 03 01 04 00 01 03 01 05 00 01 03 01 06 00 01 03 01 07 00 01 03 01 08 00 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
