#VALUE! When using countifs with dates

**jsgately** · 12-03-2012, 12:29 PM

My current formula is

=COUNTIFS(Model!AL:AL,">="&B4,Model!AL:AL,"<="&B5,OfferStatus,E6)

Where B4 and B5 are the dates
and Offer Status the name range associated with what I am trying to count.

When I do not include the OfferStatus range, it counts all the cells correctly based on the date range, but when I try to include the offerstatus I get a #Value! error.

Can someone please help.

Thanks

**dogberry** · 12-03-2012, 12:58 PM

Hi

Can you post a sample sheet.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

Chris

**Ace_XL** · 12-03-2012, 01:06 PM

The value error is on account of inconsistent ranges

While you have refernced entire column AL, your defined name range 'Offerstatus' does not refer to an entire column. Have the column AL range changed to correspond to the defined name range i.e. if Offerstatus is from rows 1:100 then

=COUNTIFS(Model!AL1:AL100,">="&B4,Model!AL1:AL100,"<="&B5,OfferStatus,E6)

**jsgately** · 12-03-2012, 01:09 PM

I cannot attach the exact spreadsheet for confidentiality purposes, however when I tried the function on a new spreadsheet it worked fine. The spreadsheet I am working with right now uses drop down boxes, could that be the problem?