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:

=AND(NUM>="C123",NUM<"C9999",CUR="USD",CO="HO")

=AND(NUM>="X122",NUM<"C9999",CUR="USD",CO="HO")

<tbody>
</tbody>

These cells show up as

#NAME?

#NAME?

<tbody>
</tbody>

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

 

[salpalpable]

OK lemme try to rephrase my question - AND() as I understand it is a function, not an operator. (Like, If x=1 And x=2 would be more operatory?) Here, AND errors out as #NAME, but works with DSUM. DSUM has to like, be reaching into it and grabbing the criteria out and applying it to its own range instead of just using what AND would evaluate to on its own.

I don't know from reading DSUM or AND's syntax how I would know that it would behave like this, as I would expect DSUM to litereally be trying to use a criteria of #NAME. I think theres some fundamental excel formula rule of thumb that I don't know about that lets it do that?

 

[Tetra201]

... I don't know from reading DSUM or AND's syntax how I would know that it would behave like this...

Some information is really hard to find and you learn it by looking at what others are doing.

If you don't like using undefined names in DSUM criteria formulas, you can use cell references. For example, if you have NUM, CUR, and CO column labels in range A1:C1, the following two criteria formulas would work the same way:

=AND(NUM>="X122",NUM<"X9999",CUR="USD",CO="HO")
=AND(A2>="X122",A2<"X9999",B2="USD",C2="HO")

However, A3, B3, and C3 would not work. Why? -- That's the way DSUM operates.

 

[salpalpable]

Dangit. Well, OK. Thanks for taking the time to respond and for the extra tips!