Countifs with Dates

[Jsleider]

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!

 

[Canapone]

Hi

possible formula

after today (>), before today (<)

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


Hope it's a little help

 

[Oeldere]

(untested), but you could try this one.

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

 

[Weazel]

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]

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

Thanks for the try!

 

[Jsleider]

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

 

[Jsleider]

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]

what is contained in the named range?

can you post a few rows of data ?

 

[Canapone]

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

 

[Jsleider]

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!