COUNTIFS with Previous date or date to come

Léonie

Hi, I'm having difficulties with the COUNTIFS formulas and dates. 

I have a table from another sheet in which I am interested in four columns (Track, Type, Due Date and Done). I would like to know the number of rows where track = "customer", type = "track action", due date = is greater than or equal to ten days from today and done= is not checked. 

Then I want to do the same thing but with due date = is less than or equal to 10 days from today. 

Here is the formula I wrote: 

=COUNTIFS ({RAID LOG Range 3}; TODAY(-10); {RAID LOG Range 1}; "Cusotmer"; {RAID LOG Range 2}; "Track Action"; {RAID LOG Range 3}; 0)

I can't seem to get the right result. Any advices ?

Paul Newcome

Try something like this...

=COUNTIFS({Date Column}, @cell >= TODAY(10), {Track Column}, @cell = "Customer", {Type Column}, @cell = "Track Action", {Checkbox Column}, @cell <> 1)

Léonie

Hi, I try this formula and it's work.

For the @cell, I think  wrote it wrong.

Anyway, thanks a lot for your advices !

Paul Newcome

Try something like this...

=COUNTIFS({Date Column}, @cell >= TODAY(10), {Track Column}, @cell = "Customer", {Type Column}, @cell = "Track Action", {Checkbox Column}, @cell <> 1)

Léonie

Thank you for your comment, i tried but I had #INVALID OPERATION...

Paul Newcome

Can you provide a screenshot of the formula in the sheet?

Léonie

Sure,

Paul Newcome

Have you double checked that the date column on the source sheet is in fact set as a date type column?

Léonie

Yes, it is a date type column. It is the "@cell" that makes #invalid operation

I can't get the number of lines between two dates...

Paul Newcome

That's odd. I use "@cell" references all the time. Is that error present anywhere in the source sheet?

Léonie

Hi, I try this formula and it's work.

For the @cell, I think  wrote it wrong.

Anyway, thanks a lot for your advices !

Paul Newcome

Happy to help. 👍️