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,A13)
and you would nor have the amounts where name - Jim and name = Fred and
Category = A or B.

--
Regards

Roger Govier


"Nick''s brother" <(E-Mail Removed)> wrote in
message news:97273C86-43E5-451F-A8D2-(E-Mail Removed)...
> 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,A13)
> and you would nor have the amounts where name - Jim and name = Fred and
> Category = A or B.
>
> --
> Regards
>
> Roger Govier
>
>
> "Nick''s brother" <(E-Mail Removed)> wrote in
> message news:97273C86-43E5-451F-A8D2-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
message news:51226D15-F07A-4A5D-BD91-(E-Mail Removed)...
> 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,A13)
>> and you would nor have the amounts where name - Jim and name = Fred
>> and
>> Category = A or B.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Nick''s brother" <(E-Mail Removed)> wrote in
>> message news:97273C86-43E5-451F-A8D2-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in
> message news:51226D15-F07A-4A5D-BD91-(E-Mail Removed)...
> > 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,A13)
> >> and you would nor have the amounts where name - Jim and name = Fred
> >> and
> >> Category = A or B.
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Nick''s brother" <(E-Mail Removed)> wrote in
> >> message news:97273C86-43E5-451F-A8D2-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in
message news:780380CE-BBCB-4529-BB88-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message news:51226D15-F07A-4A5D-BD91-(E-Mail Removed)...
>> > 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,A13)
>> >> and you would nor have the amounts where name - Jim and name =
>> >> Fred
>> >> and
>> >> Category = A or B.
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "Nick''s brother" <(E-Mail Removed)> wrote
>> >> in
>> >> message news:97273C86-43E5-451F-A8D2-(E-Mail Removed)...
>> >> > 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" <(E-Mail Removed)> wrote in
> message news:780380CE-BBCB-4529-BB88-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in
> >> message news:51226D15-F07A-4A5D-BD91-(E-Mail Removed)...
> >> > 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,A13)
> >> >> and you would nor have the amounts where name - Jim and name =
> >> >> Fred
> >> >> and
> >> >> Category = A or B.
> >> >>
> >> >> --
> >> >> Regards
> >> >>
> >> >> Roger Govier
> >> >>
> >> >>
> >> >> "Nick''s brother" <(E-Mail Removed)> wrote
> >> >> in
> >> >> message news:97273C86-43E5-451F-A8D2-(E-Mail Removed)...
> >> >> > 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" <(E-Mail Removed)> wrote in
> > message news:780380CE-BBCB-4529-BB88-(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in
> > >> message news:51226D15-F07A-4A5D-BD91-(E-Mail Removed)...
> > >> > 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,A13)
> > >> >> and you would nor have the amounts where name - Jim and name =
> > >> >> Fred
> > >> >> and
> > >> >> Category = A or B.
> > >> >>
> > >> >> --
> > >> >> Regards
> > >> >>
> > >> >> Roger Govier
> > >> >>
> > >> >>
> > >> >> "Nick''s brother" <(E-Mail Removed)> wrote
> > >> >> in
> > >> >> message news:97273C86-43E5-451F-A8D2-(E-Mail Removed)...
> > >> >> > 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" <(E-Mail Removed)> wrote in
message newsB44C457-2BBD-42C5-BF8A-(E-Mail Removed)...
> ...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" <(E-Mail Removed)> wrote
>> > in
>> > message news:780380CE-BBCB-4529-BB88-(E-Mail Removed)...
>> > > 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" <(E-Mail Removed)>
>> > >> wrote in
>> > >> message
>> > >> news:51226D15-F07A-4A5D-BD91-(E-Mail Removed)...
>> > >> > 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,A13)
>> > >> >> and you would nor have the amounts where name - Jim and name
>> > >> >> =
>> > >> >> Fred
>> > >> >> and
>> > >> >> Category = A or B.
>> > >> >>
>> > >> >> --
>> > >> >> Regards
>> > >> >>
>> > >> >> Roger Govier
>> > >> >>
>> > >> >>
>> > >> >> "Nick''s brother" <(E-Mail Removed)>
>> > >> >> wrote
>> > >> >> in
>> > >> >> message
>> > >> >> news:97273C86-43E5-451F-A8D2-(E-Mail Removed)...
>> > >> >> > 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.
>> > >> >>
>> > >> >>
>> > >> >>
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >