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