Hi, this is my first time. Sorry if this has been asked and answered already
but I couldn't find an answer to this specific question on previous posts.

I'm trying to use the DSUM function and copy the formula down a column of
all rows where my criteria data resides. The field names in row #1 and
records containing criteria starting at row #2. Is there any way that DSUM
can do this? The formula obviously works for my first data record (row 2)
that contains criteria where the criteria argument would be for example
D1:E2. But when I want to evaluate the criteria on the second data record I
haven't figured out how to both reference the field names (D$1:E$1) and the
criteria set for this specific row (D3:E3).

....and then the next record referencing the field names (D$1:E$1) and the
criteria (D4:E4) and so on.

I think it would be great to really leverage the functionality of a
spreadsheet with a basic multiple criteria join instead of having to use
Access to make this type of join.

Thanks in advance for your suggestions.

Hi

The DSUM formula should not need to be copied down.
You should have the field names on Row 1, followed by any number of
blank rows in which to enter your criteria.
Then the field names should be repeated, say on row 5, with all of you
data from row 6 say to row 100.

Suppose you have columns for Name, Category and Amount
if in A2 you entered ="=Fred"

then the formula
=DSUM(A5:C100,C1,A1:C2)
would add the Amounts for all rows where column A = Fred
If you also wanted the results for Fred and Jim, you would enter in A3
="=Jim" and amend the formula to
=DSUM(A5:C100,C1,A1:C3)

If you wanted the Amounts for Fred or Jim but only for Category A, then
inserting in B2 ="=A"
would achieve this with the same formula.

If you wanted to have an "OR" situation, then you would need to add a
farther column heading in D1 repeating Category as the heading, and
entering ="=B" in D2
Change the formula to
=DSUM(A5:C100,C1,A1:D3)
and you would nor have the amounts where name - Jim and name = Fred and
Category = A or B.

--
Regards

Roger Govier


"Nick''s brother" wrote in
message ...
Hi, this is my first time. Sorry if this has been asked and answered
already
but I couldn't find an answer to this specific question on previous
posts.

I'm trying to use the DSUM function and copy the formula down a column
of
all rows where my criteria data resides. The field names in row #1
and
records containing criteria starting at row #2. Is there any way that
DSUM
can do this? The formula obviously works for my first data record
(row 2)
that contains criteria where the criteria argument would be for
example
D1:E2. But when I want to evaluate the criteria on the second data
record I
haven't figured out how to both reference the field names (D$1:E$1)
and the
criteria set for this specific row (D3:E3).

...and then the next record referencing the field names (D$1:E$1) and
the
criteria (D4:E4) and so on.

I think it would be great to really leverage the functionality of a
spreadsheet with a basic multiple criteria join instead of having to
use
Access to make this type of join.

Thanks in advance for your suggestions.

Thank you for the post Roger. I'm afraid I didn't make my particular
question clear enough. The reason I want to copy the DSUM formula down a
column is I would like a "dynamic" criteria list that gives me a distinct
DSUM result based on the criteria values located within the row of each DSUM
formula. Let's say that the data being evaluated by DSUM is in another sheet
or tab.

Sort of like having a list of say 100 query requests to evaluate against the
data, where each of the 100 lines has the input variables or filters used in
each separate query. This would leverage the functionality of both a
spreadsheet and a database at the same time.

Thanks,
Nick's brother

"Roger Govier" wrote:

Hi

The DSUM formula should not need to be copied down.
You should have the field names on Row 1, followed by any number of
blank rows in which to enter your criteria.
Then the field names should be repeated, say on row 5, with all of you
data from row 6 say to row 100.

Suppose you have columns for Name, Category and Amount
if in A2 you entered ="=Fred"

then the formula
=DSUM(A5:C100,C1,A1:C2)
would add the Amounts for all rows where column A = Fred
If you also wanted the results for Fred and Jim, you would enter in A3
="=Jim" and amend the formula to
=DSUM(A5:C100,C1,A1:C3)

If you wanted the Amounts for Fred or Jim but only for Category A, then
inserting in B2 ="=A"
would achieve this with the same formula.

If you wanted to have an "OR" situation, then you would need to add a
farther column heading in D1 repeating Category as the heading, and
entering ="=B" in D2
Change the formula to
=DSUM(A5:C100,C1,A1:D3)
and you would nor have the amounts where name - Jim and name = Fred and
Category = A or B.

--
Regards

Roger Govier


"Nick''s brother" wrote in
message ...
Hi, this is my first time. Sorry if this has been asked and answered
already
but I couldn't find an answer to this specific question on previous
posts.

I'm trying to use the DSUM function and copy the formula down a column
of
all rows where my criteria data resides. The field names in row #1
and
records containing criteria starting at row #2. Is there any way that
DSUM
can do this? The formula obviously works for my first data record
(row 2)
that contains criteria where the criteria argument would be for
example
D1:E2. But when I want to evaluate the criteria on the second data
record I
haven't figured out how to both reference the field names (D$1:E$1)
and the
criteria set for this specific row (D3:E3).

...and then the next record referencing the field names (D$1:E$1) and
the
criteria (D4:E4) and so on.

I think it would be great to really leverage the functionality of a
spreadsheet with a basic multiple criteria join instead of having to
use
Access to make this type of join.

Thanks in advance for your suggestions.

Hi

Then take a look at the Sumproduct function.
Something like
=SUMPRODUCT(($A$2:$A$100="criteria1")*($B$2:$B$100 ="criteria2")*$C2:$C100)
you can change your criteria on each row, or have the criteria located
in other cells.

for a great explanation on how Sumproduct works, take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Nick''''s brother" wrote in
message ...
Thank you for the post Roger. I'm afraid I didn't make my particular
question clear enough. The reason I want to copy the DSUM formula
down a
column is I would like a "dynamic" criteria list that gives me a
distinct
DSUM result based on the criteria values located within the row of
each DSUM
formula. Let's say that the data being evaluated by DSUM is in
another sheet
or tab.

Sort of like having a list of say 100 query requests to evaluate
against the
data, where each of the 100 lines has the input variables or filters
used in
each separate query. This would leverage the functionality of both a
spreadsheet and a database at the same time.

Thanks,
Nick's brother

"Roger Govier" wrote:

Hi

The DSUM formula should not need to be copied down.
You should have the field names on Row 1, followed by any number of
blank rows in which to enter your criteria.
Then the field names should be repeated, say on row 5, with all of
you
data from row 6 say to row 100.

Suppose you have columns for Name, Category and Amount
if in A2 you entered ="=Fred"

then the formula
=DSUM(A5:C100,C1,A1:C2)
would add the Amounts for all rows where column A = Fred
If you also wanted the results for Fred and Jim, you would enter in
A3
="=Jim" and amend the formula to
=DSUM(A5:C100,C1,A1:C3)

If you wanted the Amounts for Fred or Jim but only for Category A,
then
inserting in B2 ="=A"
would achieve this with the same formula.

If you wanted to have an "OR" situation, then you would need to add a
farther column heading in D1 repeating Category as the heading, and
entering ="=B" in D2
Change the formula to
=DSUM(A5:C100,C1,A1:D3)
and you would nor have the amounts where name - Jim and name = Fred
and
Category = A or B.

--
Regards

Roger Govier


"Nick''s brother" wrote in
message ...
Hi, this is my first time. Sorry if this has been asked and
answered
already
but I couldn't find an answer to this specific question on previous
posts.

I'm trying to use the DSUM function and copy the formula down a
column
of
all rows where my criteria data resides. The field names in row #1
and
records containing criteria starting at row #2. Is there any way
that
DSUM
can do this? The formula obviously works for my first data record
(row 2)
that contains criteria where the criteria argument would be for
example
D1:E2. But when I want to evaluate the criteria on the second data
record I
haven't figured out how to both reference the field names (D$1:E$1)
and the
criteria set for this specific row (D3:E3).

...and then the next record referencing the field names (D$1:E$1)
and
the
criteria (D4:E4) and so on.

I think it would be great to really leverage the functionality of a
spreadsheet with a basic multiple criteria join instead of having
to
use
Access to make this type of join.

Thanks in advance for your suggestions.

Thanks !!!!!! This works and the best thing is it makes it simple!!!

Admittedly, when I first read it I didn't try it because I thought "I don't
want to multiply anything". Then I read your web link and saw the light.

I had some problems with text data but used the -- things and that worked
great as well. I notice that even with the -- things it still doesn't like
the column headings included in the range of the array so I only include the
rows with data.

By the way, I found some other post around here that shows how to use DSUM
with OFFSET and the Data/Table menu option. That worked too, and was very
creative but I'll probably use this because it is a simple one step function.


Do you know if this SUMPRODUCT can aggregate with a MIN or MAX text value
from one of the arrays it is evaluating and return the min or max text?

Thanks,
Nick's brother


"Roger Govier" wrote:

Hi

Then take a look at the Sumproduct function.
Something like
=SUMPRODUCT(($A$2:$A$100="criteria1")*($B$2:$B$100 ="criteria2")*$C2:$C100)
you can change your criteria on each row, or have the criteria located
in other cells.

for a great explanation on how Sumproduct works, take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Nick''''s brother" wrote in
message ...
Thank you for the post Roger. I'm afraid I didn't make my particular
question clear enough. The reason I want to copy the DSUM formula
down a
column is I would like a "dynamic" criteria list that gives me a
distinct
DSUM result based on the criteria values located within the row of
each DSUM
formula. Let's say that the data being evaluated by DSUM is in
another sheet
or tab.

Sort of like having a list of say 100 query requests to evaluate
against the
data, where each of the 100 lines has the input variables or filters
used in
each separate query. This would leverage the functionality of both a
spreadsheet and a database at the same time.

Thanks,
Nick's brother

"Roger Govier" wrote:

Hi

The DSUM formula should not need to be copied down.
You should have the field names on Row 1, followed by any number of
blank rows in which to enter your criteria.
Then the field names should be repeated, say on row 5, with all of
you
data from row 6 say to row 100.

Suppose you have columns for Name, Category and Amount
if in A2 you entered ="=Fred"

then the formula
=DSUM(A5:C100,C1,A1:C2)
would add the Amounts for all rows where column A = Fred
If you also wanted the results for Fred and Jim, you would enter in
A3
="=Jim" and amend the formula to
=DSUM(A5:C100,C1,A1:C3)

If you wanted the Amounts for Fred or Jim but only for Category A,
then
inserting in B2 ="=A"
would achieve this with the same formula.

If you wanted to have an "OR" situation, then you would need to add a
farther column heading in D1 repeating Category as the heading, and
entering ="=B" in D2
Change the formula to
=DSUM(A5:C100,C1,A1:D3)
and you would nor have the amounts where name - Jim and name = Fred
and
Category = A or B.

--
Regards

Roger Govier


"Nick''s brother" wrote in
message ...
Hi, this is my first time. Sorry if this has been asked and
answered
already
but I couldn't find an answer to this specific question on previous
posts.

I'm trying to use the DSUM function and copy the formula down a
column
of
all rows where my criteria data resides. The field names in row #1
and
records containing criteria starting at row #2. Is there any way
that
DSUM
can do this? The formula obviously works for my first data record
(row 2)
that contains criteria where the criteria argument would be for
example
D1:E2. But when I want to evaluate the criteria on the second data
record I
haven't figured out how to both reference the field names (D$1:E$1)
and the
criteria set for this specific row (D3:E3).

...and then the next record referencing the field names (D$1:E$1)
and
the
criteria (D4:E4) and so on.

I think it would be great to really leverage the functionality of a
spreadsheet with a basic multiple criteria join instead of having
to
use
Access to make this type of join.

Thanks in advance for your suggestions.

Hi Nick

Glad you have "seen the light".
Not sure what you mean about min or max text.
You could use something like
=SUMPRODUCT(--(LEFT(A1:A10,1)="B"),--(LEFT(A1:A10,1)<="M"),B1:B10)

or
=SUMPRODUCT(--(ISNUMBER(FIND("Z",A1:A10))),B1:B10)
this looks for a letter "Z "anywhere within each cell of A1:A10. Note
that FIND is case sensitive and would not find "z".
If you were not concerned about case, then you could use SERACH() in
place of FIND()

By the way, how is Nick<bg

--
Regards

Roger Govier


"Nick''''s brother" wrote in
message ...
Thanks !!!!!! This works and the best thing is it makes it simple!!!

Admittedly, when I first read it I didn't try it because I thought "I
don't
want to multiply anything". Then I read your web link and saw the
light.

I had some problems with text data but used the -- things and that
worked
great as well. I notice that even with the -- things it still doesn't
like
the column headings included in the range of the array so I only
include the
rows with data.

By the way, I found some other post around here that shows how to use
DSUM
with OFFSET and the Data/Table menu option. That worked too, and was
very
creative but I'll probably use this because it is a simple one step
function.


Do you know if this SUMPRODUCT can aggregate with a MIN or MAX text
value
from one of the arrays it is evaluating and return the min or max
text?

Thanks,
Nick's brother


"Roger Govier" wrote:

Hi

Then take a look at the Sumproduct function.
Something like
=SUMPRODUCT(($A$2:$A$100="criteria1")*($B$2:$B$100 ="criteria2")*$C2:$C100)
you can change your criteria on each row, or have the criteria
located
in other cells.

for a great explanation on how Sumproduct works, take a look at
http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Regards

Roger Govier


"Nick''''s brother" wrote in
message ...
Thank you for the post Roger. I'm afraid I didn't make my
particular
question clear enough. The reason I want to copy the DSUM formula
down a
column is I would like a "dynamic" criteria list that gives me a
distinct
DSUM result based on the criteria values located within the row of
each DSUM
formula. Let's say that the data being evaluated by DSUM is in
another sheet
or tab.

Sort of like having a list of say 100 query requests to evaluate
against the
data, where each of the 100 lines has the input variables or
filters
used in
each separate query. This would leverage the functionality of both
a
spreadsheet and a database at the same time.

Thanks,
Nick's brother

"Roger Govier" wrote:

Hi

The DSUM formula should not need to be copied down.
You should have the field names on Row 1, followed by any number
of
blank rows in which to enter your criteria.
Then the field names should be repeated, say on row 5, with all of
you
data from row 6 say to row 100.

Suppose you have columns for Name, Category and Amount
if in A2 you entered ="=Fred"

then the formula
=DSUM(A5:C100,C1,A1:C2)
would add the Amounts for all rows where column A = Fred
If you also wanted the results for Fred and Jim, you would enter
in
A3
="=Jim" and amend the formula to
=DSUM(A5:C100,C1,A1:C3)

If you wanted the Amounts for Fred or Jim but only for Category A,
then
inserting in B2 ="=A"
would achieve this with the same formula.

If you wanted to have an "OR" situation, then you would need to
add a
farther column heading in D1 repeating Category as the heading,
and
entering ="=B" in D2
Change the formula to
=DSUM(A5:C100,C1,A1:D3)
and you would nor have the amounts where name - Jim and name =
Fred
and
Category = A or B.

--
Regards

Roger Govier


"Nick''s brother" wrote
in
message ...
Hi, this is my first time. Sorry if this has been asked and
answered
already
but I couldn't find an answer to this specific question on
previous
posts.

I'm trying to use the DSUM function and copy the formula down a
column
of
all rows where my criteria data resides. The field names in row
#1
and
records containing criteria starting at row #2. Is there any
way
that
DSUM
can do this? The formula obviously works for my first data
record
(row 2)
that contains criteria where the criteria argument would be for
example
D1:E2. But when I want to evaluate the criteria on the second
data
record I
haven't figured out how to both reference the field names
(D$1:E$1)
and the
criteria set for this specific row (D3:E3).

...and then the next record referencing the field names
(D$1:E$1)
and
the
criteria (D4:E4) and so on.

I think it would be great to really leverage the functionality
of a
spreadsheet with a basic multiple criteria join instead of
having
to
use
Access to make this type of join.

Thanks in advance for your suggestions.