| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
DBSUM function but with function as criterion, not a range
Let me try to give a simple example of what I want:
I have two worksheets: "WorkSheet1" and "WorkSheet2". WorkSheet1 contains a column with as column header "IDNr.". WorkSheet2 contains 2 columns: "IDNr." and "Amount". The IDNr refers to the IDNr. on WorkSheet1 and the Amount column is used to add an amount. On WorkSheet1 I want to add a second column, in which all Amounts from WorkSheet2 are summed which have the same IDNr. as the specific row in WorkSheet1. So: WorkSheet1 IDNr. - Totals 1 - 11 (5+4+2) 2 - 8 (3+5) WorkSheet2: IDNr. - Amount 1 - 5 2 - 3 1 - 4 1 - 2 2 - 5 I tried to use DMSUM('WorkSheet2'!A1:B5;2;A1:A2) on WorkSheet1. This works fine for B2, but when I want to do B3: I get the following: DMSUM('WorkSheet2'!A1:B5;2;A1:A3), which gives me the sum of all appearences on WorkSheet2 having 1 or 2 as ID. Can anybody help me out here? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DBSUM function but with function as criterion, not a range
Using your posted example...
Try this on Sheet1: A1: IDNr A2: 1 A3: 2 B1: Amount B2: =SUMIF(Sheet2!$A$1:$A$10,Sheet1!A2,Sheet2!$B$1:$B$ 10) (Copy that formula down through B3) With your sample data B2 returns 11 B3 returns 8 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "corne_mo" wrote: Let me try to give a simple example of what I want: I have two worksheets: "WorkSheet1" and "WorkSheet2". WorkSheet1 contains a column with as column header "IDNr.". WorkSheet2 contains 2 columns: "IDNr." and "Amount". The IDNr refers to the IDNr. on WorkSheet1 and the Amount column is used to add an amount. On WorkSheet1 I want to add a second column, in which all Amounts from WorkSheet2 are summed which have the same IDNr. as the specific row in WorkSheet1. So: WorkSheet1 IDNr. - Totals 1 - 11 (5+4+2) 2 - 8 (3+5) WorkSheet2: IDNr. - Amount 1 - 5 2 - 3 1 - 4 1 - 2 2 - 5 I tried to use DMSUM('WorkSheet2'!A1:B5;2;A1:A2) on WorkSheet1. This works fine for B2, but when I want to do B3: I get the following: DMSUM('WorkSheet2'!A1:B5;2;A1:A3), which gives me the sum of all appearences on WorkSheet2 having 1 or 2 as ID. Can anybody help me out here? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DBSUM function but with function as criterion, not a range
Thanks Ron,
I was so pinned to the DBSUM function that I coulnd't find an other function. Thanks again. Works perfectly. Cheers, Corné "Ron Coderre" wrote: Using your posted example... Try this on Sheet1: A1: IDNr A2: 1 A3: 2 B1: Amount B2: =SUMIF(Sheet2!$A$1:$A$10,Sheet1!A2,Sheet2!$B$1:$B$ 10) (Copy that formula down through B3) With your sample data B2 returns 11 B3 returns 8 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "corne_mo" wrote: Let me try to give a simple example of what I want: I have two worksheets: "WorkSheet1" and "WorkSheet2". WorkSheet1 contains a column with as column header "IDNr.". WorkSheet2 contains 2 columns: "IDNr." and "Amount". The IDNr refers to the IDNr. on WorkSheet1 and the Amount column is used to add an amount. On WorkSheet1 I want to add a second column, in which all Amounts from WorkSheet2 are summed which have the same IDNr. as the specific row in WorkSheet1. So: WorkSheet1 IDNr. - Totals 1 - 11 (5+4+2) 2 - 8 (3+5) WorkSheet2: IDNr. - Amount 1 - 5 2 - 3 1 - 4 1 - 2 2 - 5 I tried to use DMSUM('WorkSheet2'!A1:B5;2;A1:A2) on WorkSheet1. This works fine for B2, but when I want to do B3: I get the following: DMSUM('WorkSheet2'!A1:B5;2;A1:A3), which gives me the sum of all appearences on WorkSheet2 having 1 or 2 as ID. Can anybody help me out here? Thanks in advance. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
DBSUM function but with function as criterion, not a range
Thanks for the feedback....I'm glad that worked for you.
*********** Regards, Ron XL2002, WinXP "corne_mo" wrote: Thanks Ron, I was so pinned to the DBSUM function that I coulnd't find an other function. Thanks again. Works perfectly. Cheers, Corné "Ron Coderre" wrote: Using your posted example... Try this on Sheet1: A1: IDNr A2: 1 A3: 2 B1: Amount B2: =SUMIF(Sheet2!$A$1:$A$10,Sheet1!A2,Sheet2!$B$1:$B$ 10) (Copy that formula down through B3) With your sample data B2 returns 11 B3 returns 8 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "corne_mo" wrote: Let me try to give a simple example of what I want: I have two worksheets: "WorkSheet1" and "WorkSheet2". WorkSheet1 contains a column with as column header "IDNr.". WorkSheet2 contains 2 columns: "IDNr." and "Amount". The IDNr refers to the IDNr. on WorkSheet1 and the Amount column is used to add an amount. On WorkSheet1 I want to add a second column, in which all Amounts from WorkSheet2 are summed which have the same IDNr. as the specific row in WorkSheet1. So: WorkSheet1 IDNr. - Totals 1 - 11 (5+4+2) 2 - 8 (3+5) WorkSheet2: IDNr. - Amount 1 - 5 2 - 3 1 - 4 1 - 2 2 - 5 I tried to use DMSUM('WorkSheet2'!A1:B5;2;A1:A2) on WorkSheet1. This works fine for B2, but when I want to do B3: I get the following: DMSUM('WorkSheet2'!A1:B5;2;A1:A3), which gives me the sum of all appearences on WorkSheet2 having 1 or 2 as ID. Can anybody help me out here? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple Criterion in a SUMIF function | Excel Worksheet Functions | |||
| Sum a range based on a criterion | Excel Worksheet Functions | |||
| Range function | Excel Worksheet Functions | |||
| Multiple criterion including wildcards to sum in a range | Excel Worksheet Functions | |||
| How do I put more than one criterion in a SUMIF function? | Excel Worksheet Functions | |||
Linear Mode
