# Countifs with Dates

#### Jsleider

##### New Member
Is it possible to use the Countifs function with dates? Say I have thirty customers expecting a delivery. I already have a sheet that runs the customer's expected delivery date. I am trying to run some countifs with a date range to single out the customer's that have not received delivery but are before today's delivery date, after today's delivery, and expecting delivery today.

For the customer's expecting it today, I use the below formula and it works perfect.

=Countifs(Data,"Not Delivered",Data,Today())

But I am trying to figure out if I can do one almost the same, but for deliveries before today's date and after today's date.

=Countifs(Data,"Not Delivered",Data,??????)

I appreciate any and all help. I am not sure if this is even possible. Thanks!

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Canapone

##### Active Member
Hi

possible formula

after today (>), before today (<)

=Countifs(Data,"Not Delivered",Data,">"&Today())

Hope it's a little help

#### Oeldere

##### Well-known Member
(untested), but you could try this one.

=Countifs(Data,"Not Delivered",Data,<>Today())

#### Weazel

##### Well-known Member
is DATA a named range or are you using it as a placeholder ?

I ask because you couldn't have "not delivered" and dates in the same range and have it work correctly.

#### Jsleider

##### New Member
I tried this yesterday, but it gives me the error message "the formula you typed contains an error"

Thanks for the try!

#### Jsleider

##### New Member
Data is a named range, Not Delivered is what a line states if it is not delivered.

#### Jsleider

##### New Member
Hi

possible formula

after today (>), before today (<)

=Countifs(Data,"Not Delivered",Data,">"&Today())

Hope it's a little help

It seems like it worked, but when I attempted to verify the data , it seems to be pulling more results than it should. I am am still working to see if there is an error with my data. This might have worked.

#### Weazel

##### Well-known Member
what is contained in the named range?

can you post a few rows of data ?

#### Canapone

##### Active Member
Hi again

in dates column, empty cells can produce tricky results in formula

=Countifs(Data,"Not Delivered",Data,"<"&Today())

a naive patch could be

=Countifs(Data,"Not Delivered",Data,"<"&Today(),Data,"<>"&"")

Hope it helps

Last edited:

#### Jsleider

##### New Member
Hi again

in dates column, empty cells can produce tricky results in formula

=Countifs(Data,"Not Delivered",Data,"<"&Today())

a naive patch could be

=Countifs(Data,"Not Delivered",Data,"<"&Today(),Data,"<>"&"")

Hope it helps

I had a couple blank fields and didn't realize it. It works. Thank you so much. I have never used the & function before in Excel, I appreciate you teaching me something new!

Replies
1
Views
275
Replies
7
Views
1K
Replies
2
Views
164
Replies
0
Views
577
Replies
0
Views
601

1,191,707
Messages
5,988,225
Members
440,139
Latest member
ngaicuong2017

### 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.

### Which adblocker are you using?

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

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