COUNTIFS with start date, end date and multiple criteria

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!

Find more posts tagged with

Formulas

Accepted answers

Courtney S.

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

@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())

All comments

Courtney S.

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?

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

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

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

{Activity End},>TODAY()

)

Chris_USJ

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

@Chris_USJ I'm sorry I accidentally gave you a bad formula, I'm glad you were able to solve it and that it worked for you!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.