DSUM with AND range as Critera

salpalpable

New Member
Joined
Jun 6, 2017
Messages
7
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!!
 
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?
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
... 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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top