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:

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

ukmikeb

Well-known Member
Joined
Jul 10, 2009
Messages
2,756
Welcome to MrExcel forum.

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

hth
 

inactiveuserps07

Banned user
Joined
May 25, 2017
Messages
748
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

Well-known Member
Joined
Feb 16, 2013
Messages
6,723
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

New Member
Joined
Jun 6, 2017
Messages
7
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? :confused:
 

salpalpable

New Member
Joined
Jun 6, 2017
Messages
7
Whoops, on 1) the latter range should be B13:C13 and 2) formula should say =AND(Tree=D6) w/o quotes
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,472
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

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

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,472
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:
Code:
|NUM   |NUM    |CUR    |CO    |
|>=X122|< X9999|="=USD"|="=HO"|
<x9999|="=usd"|="=ho"|[ html="" CODE]<=""></x9999|="=usd"|="=ho"|[>
 
Last edited:

Forum statistics

Threads
1,082,318
Messages
5,364,524
Members
400,804
Latest member
davileal

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top