DSUM - non contiguous criteria range

[Guest]

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.

 

[Roger Govier]

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.

 

[Guest]

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]

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

 

[Guest]

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]

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>

 

[Guest]

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,

 

[Guest]

....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.

 

[Roger Govier]

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)

 

[ariadie]

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

Thanks a lot. Looking a formula to count rows that matching two criteria at different pairs of columns. Before, I tried to use DCOUNT, but using SUMPRODUCT is a far better solution.