DSUM with AND range as Critera

[salpalpable]

Hi guys, I'm newish to excel and this forum, please let me know if I'm posting in error, but I'm having a hard time googling this one out.

I've inherited a spreadhseet with a Named Range Called "Criteria" which includes columns names NUM, USD, CO and NET_AMT and their data. This is the only named range, the columns aren't named.

I have the following formula: =SUM(Criteria,"NET_AMT",O15:O16) which sums up the NET_AMT with the criteria in O16.

O15:O16 are as follows:

[TABLE="width: 500"]
<tbody>[TR]
[TD]=AND(NUM>="C123",NUM<"C9999",CUR="USD",CO="HO")[/TD]
[/TR]
[TR]
[TD]=AND(NUM>="X122",NUM<"C9999",CUR="USD",CO="HO")[/TD]
[/TR]
</tbody>[/TABLE]


These cells show up as
[TABLE="width: 500"]
<tbody>[TR]
[TD]#NAME?[/TD]
[/TR]
[TR]
[TD]#NAME?[/TD]
[/TR]
</tbody>[/TABLE]
presumably because NUM and CUR aren't real references/object?

Can anyone tell me what's going on?

It seems to only filter by O16, but the range doesnt work without O15, and doesn't care if O15 is blank. Why does NUM<"C9999" work as a valid criteria even though NUM is only a column name value and not a named range? Why is everything in "" ? Why does AND function as a way to express multiple criteria even though it evaluates as a boolean?

Thanks!!

 

[ukmikeb]

Welcome to MrExcel forum.

It is showing the error because you don't have a column with the header "CUR".

hth

 

[salpalpable]

Sure, but why does it still work if that's the case?

 

[inactiveuserps07]

I'd really have to see what you're looking at. Could you post an image of the pertinent section of the sheet? Then, I suspect, people will be able to help you.

 

[FDibbins]

I have the following formula: =SUM(Criteria,"NET_AMT",O15:O16) which sums up the NET_AMT with the criteria in O16.

Are you sure that is SUM() and not SUMIF()?

 

[salpalpable]

I made this while I was trying to figure out was going on, hope this helps:

My Questions:
1) Why do I need to use range B12:C13 instead of must B12:C13? Its a #VALUE error with only one row and the first row doesn't seem to matter.
2) Why is everything in quotes? For C13, =AND(Tree="D6") doesn't seem to work even if D6 is "Apple" or ="=Apple", is there a way around there?
3) How Is DSUM using arguments inside of an AND function as criteria?
4) What is it about column headers that lets me reference them in Age="Meow" but still triggers #NAME error?

Does this make sense?

 

[salpalpable]

Whoops, on 1) the latter range should be B13:C13 and 2) formula should say =AND(Tree=D6) w/o quotes

 

[Tetra201]

Here are the answers to quuestions from Post #1:

... These cells show up as
#NAME?

#NAME?

presumably because NUM and CUR aren't real references/object?..

Correct. Names NUM, CUR, and CO are not defined.

... It seems to only filter by O16, but the range doesnt work without O15, and doesn't care if O15 is blank...

You have to have at least two adjacent cells in the same column as a criteria range. This is because the DSUM description states that "You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label in which you specify a condition for the column."

... Why does NUM<"C9999" work as a valid criteria even though NUM is only a column name value and not a named range?..

This is because DSUM is capable of handling such a syntax.

... Why is everything in "" ?..

This is because these values are strings.

... Why does AND function as a way to express multiple criteria even though it evaluates as a boolean?

The AND() tells DSUM to sum only those values from the NET_AMT column that satisfy all the criteria. The OR() can be used to sum the values that satisfy at least one of the criteria.

 

[salpalpable]

OK!! Looks like I should have read the syntax better for the basic DSUM questions, my bad, thanks for answering.

I'm still a little confused on how AND() tells DSUM to evaluate those conditions, from other backgrounds would have guessed that AND would evaluate separately (to #name I guess?) and then DSUM would just be getting a "TRUE" or "FALSE" for a criteria. Is there some aspect of the AND operator that's doing this, or is there more of a general excel formula order of operation kinda thing I should read up on?

 

[Tetra201]

Not sure I understand your question.

Having any value/formula in O15 and =AND(NUM>="X122",NUM<"X9999",CUR="USD",CO="HO") in O16 is equivalent to using the following criteria range:

|NUM   |NUM    |CUR    |CO    |
|>=X122|< X9999|="=USD"|="=HO"|

<x9999|="=usd"|="=ho"|[ html="" CODE]<=""></x9999|="=usd"|="=ho"|[>