Sumifs with date range returning #VALUE error

[Derrick]

I am trying to sum an invoice amount within a given month based on a
location. I am working with named ranges as well to try and simplfy this for
myself. But when trying to use the dates I am getting the #VALUE error.
Please help me with correcting this. The formula I am trying to use is

=sumifs(INVOICED_AMOUNTS,A3:A500,">=N4",A3:A500,"<=N5",Location,"New York")

Where INVOICED_AMOUNTS (J3:J500) are where I have my dollar totals
A3:A500 is the column where dates are kept (in mmm/dd format)
N4 is my start date of 1/1/2010
N5 is my end date of 1/31/2010
Location (B3:B500) is where a list of city's are kept.

What am I doing wrong???

 

[Bob Phillips]

Try

=SUMIFS(INVOICED_AMOUNTS,A3:A500,">="&N4,A3:A500,"<="&N5,Location,"New
York")

 

[Fred Smith]

To use a cells for the criteria, do it this way:
=sumifs(INVOICED_AMOUNTS,A3:A500,">="&N4,A3:A500,"<="&N5,Location,"New
York")

Regards
Fred

 

[Derrick]

Using either of these two formulas it is still returning a #VALUE error. The
amounts I am adding, are dollar amounts, and the columns are formatted to
show $XXX.XX is the dollar sign throwing things off in the formula
calculations? Is it reading the $ as text? Not sure what else the problem
may be.

 

[Ron Rosenfeld]

Using either of these two formulas it is still returning a #VALUE error. The
amounts I am adding, are dollar amounts, and the columns are formatted to
show $XXX.XX is the dollar sign throwing things off in the formula
calculations? Is it reading the $ as text? Not sure what else the problem
may be.

A likely cause is that, with your mix of NAMED and not-named ranges, that all
of your ranges are not exactly the same size.
--ron

 

[Fred Smith]

The format is unimportant. Formats affect only the display of a cell, not
its underlying value.

Are you sure your ranges are all the same size?

Regards,
Fred