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.

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.

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.

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.