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.

Hi Roger -

Thanks for all of your helpful advice. To clarify about the text,
specifically what I would like to be able to do is have my formula bring back
text from one column in a range instead of a number based off of multiple
criteria referenced from the formula row to their corresponding columns in
the range dataset. You have shown me how to do the multiple criteria
evaluation per line using SUMPRODUCT to return a number. Now I wonder if I
can return a text from the data. Since SUMPRODUCT and the type of
evaluations of data that I am considering are evaluating to TRUE for multiple
lines of data, I will have to use an aggregate or some first value or last
value matched to bring in a text since the formula result can only be one
value. Now that I think about it, MIN() and MAX() aggregate would be silly
since they don't return text.

I took your suggested formula and modified but it gives a #value! error:
=SUMPRODUCT(--(sheet2!A$2:A$10=A2)*--(sheet2!B$2:B$10=B2),--LEFT(sheet2!C$2:C$10))
where sheet2!C2:C10 contains text values.

I hope I made my question a bit clearer. Sorry for the confusion.

Thanks,




"Roger Govier" wrote:

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.

....oops, forgot the text lenght in the example. Here's what I meant:
I took your suggested formula and modified but it gives a #value! error:
=SUMPRODUCT(--(sheet2!A$2:A$10=A2)*--(sheet2!B$2:B$10=B2),--LEFT(sheet2!C$2:C$10,5))
where sheet2!C2:C10 contains text values.

"Nick''''s brother" wrote:

Hi Roger -

Thanks for all of your helpful advice. To clarify about the text,
specifically what I would like to be able to do is have my formula bring back
text from one column in a range instead of a number based off of multiple
criteria referenced from the formula row to their corresponding columns in
the range dataset. You have shown me how to do the multiple criteria
evaluation per line using SUMPRODUCT to return a number. Now I wonder if I
can return a text from the data. Since SUMPRODUCT and the type of
evaluations of data that I am considering are evaluating to TRUE for multiple
lines of data, I will have to use an aggregate or some first value or last
value matched to bring in a text since the formula result can only be one
value. Now that I think about it, MIN() and MAX() aggregate would be silly
since they don't return text.

I took your suggested formula and modified but it gives a #value! error:
=SUMPRODUCT(--(sheet2!A$2:A$10=A2)*--(sheet2!B$2:B$10=B2),--LEFT(sheet2!C$2:C$10))
where sheet2!C2:C10 contains text values.

I hope I made my question a bit clearer. Sorry for the confusion.

Thanks,




"Roger Govier" wrote:

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.

Hi

Then maybe its not Sumproduct you need to use at all but

=LEFT(INDEX($C$2:$C$10,MATCH(1,($A$2:$A140=A2)*($B $2:$B$10=B2),0)),5)

--
Regards

Roger Govier


"Nick''''s brother" wrote in
message ...
...oops, forgot the text lenght in the example. Here's what I meant:
I took your suggested formula and modified but it gives a #value!
error:
=SUMPRODUCT(--(sheet2!A$2:A$10=A2)*--(sheet2!B$2:B$10=B2),--LEFT(sheet2!C$2:C$10,5))
where sheet2!C2:C10 contains text values.

"Nick''''s brother" wrote:

Hi Roger -

Thanks for all of your helpful advice. To clarify about the text,
specifically what I would like to be able to do is have my formula
bring back
text from one column in a range instead of a number based off of
multiple
criteria referenced from the formula row to their corresponding
columns in
the range dataset. You have shown me how to do the multiple criteria
evaluation per line using SUMPRODUCT to return a number. Now I
wonder if I
can return a text from the data. Since SUMPRODUCT and the type of
evaluations of data that I am considering are evaluating to TRUE for
multiple
lines of data, I will have to use an aggregate or some first value or
last
value matched to bring in a text since the formula result can only be
one
value. Now that I think about it, MIN() and MAX() aggregate would be
silly
since they don't return text.

I took your suggested formula and modified but it gives a #value!
error:
=SUMPRODUCT(--(sheet2!A$2:A$10=A2)*--(sheet2!B$2:B$10=B2),--LEFT(sheet2!C$2:C$10))
where sheet2!C2:C10 contains text values.

I hope I made my question a bit clearer. Sorry for the confusion.

Thanks,




"Roger Govier" wrote:

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.