COUNTIFS with start date, end date and multiple criteria

Chris_USJ · 03/28/24

Hi all,

I tried searching for this and I found a few things that came close but this formula request has a bit of an extra layer. I have a report that is currently achieving this and now I need to write a formula in order to put that number on a dashboard.

The reports filter criteria is as follows:

If "Status" is not one of "Completed", "Cancelled" AND "Activity Begin" is today AND "Activity End" is in the future

OR If "Status" is not one of "Completed", "Cancelled" AND "Activity Begin" is in the next 30 days AND "Activity End" is in the future

OR If "Status" is not one of "Completed", "Cancelled" AND "Activity Begin" is in the past AND "Activity End" is in the future

I have all 9 of those filters on the report and it returns me the correct value. But I cant seem to figure out how to manage all that with a COUNTIFS formula.

This is what I was experimenting with, but it isn't working correctly. Its not counting items that already started at an earlier date and end on a future date.

For additional context, my other "Status" options are "Active" and "Upcoming"

=COUNTIFS({Activity Begin}, AND(@cell >= TODAY(), @cell <= TODAY(+30)), {Status}, OR(@cell = "Active", @cell = "Upcoming"))

Any help is appreciated!

Courtney S. · 03/28/24

It seems like the 3 groups of filters have identical criteria for the Status and Activity End columns, and the only thing changing is the Activity Begin?

For Activity Begin I'm seeing that the 3 different criteria are: Today, or in the next 30 days, or any time in the past. In that case, it seems to me that you can just condense those 3 into the one criterion that Activity Begin is <= TODAY(+30).

So you'd have something like this: =COUNTIFS(

{Status}, OR("Active", "Upcoming"),

{Activity Begin},<=TODAY(30),

{Activity End},>TODAY()

)

Chris_USJ · 03/28/24

@Courtney S. Thank you for your response. ~~I have tried your formula and I keep receiving Invalid Operation.~~

Edit: I had to add the @cell = into the formula you gave and that worked!

Thank you for the help!!!

Here it is copied from my sheet:

=COUNTIFS({Status}, OR(@cell = "Active", @cell = "Upcoming"), {Activity Begin}, <=TODAY(30), {Activity End}, >TODAY())

Courtney S. · 03/28/24