Using Countifs Formulas to Count Number of Dates in a Date Range

Hello - Hoping for some help on my formula below.

I am trying to count the number of dates (at a specific location) that are between 30 and 60 days from today.

Here is my current formula:

=COUNTIFS({BTP MASTER 2024 Range 1}, [Location Name]@row, {BTP MASTER 2024 Range 3}, "",{BTPMASTER 2024 Range 4}, <TODAY(+60), {BTP MASTER 2024 Range 4}, >TODAY(+30), {BTP MASTER 2024 Range 5}, 0)

The first part of the formula is referencing a master sheet, and searching for a specific location. Then it is searching for blanks in another range on that sheet (that signifies the item is open). That is all working fine. My issue is on the last half of the formula where I'm trying to see what is open between 30 and 60 days.

The current formula is returning a number but I know that number is incorrect when I manually count the dates.

For reference, I'm also using the below formula to count items open in the last 7 days and it's working correctly.

=COUNTIFS({BTP MASTER 2024 Range 1}, [Location Name]@row, {BTP MASTER 2024 Range 3}, "", {BTP MASTER 2024 Range 4}, >TODAY(-8), {BTP MASTER 2024 Range 4}, <TODAY(+1), {BTP MASTER 2024 Range 5}, 0)

Thanks in advance for any help!

-Nick

Find more posts tagged with

Countif

Accepted answers

All comments

Carson Penticuff

Was this a direct copy/paste? If so, this will cause an issue: ,{BTPMASTER 2024 Range 4}

Nrob14

Hi Carson,

Thanks for the quick reply. That was a direct copy and paste. But I'm not sure I follow what's wrong with the ",{BTPMASTER 2024 Range 4}" you referenced? That range is the column that contains the created dates on the master sheet. Sorry if I'm missing something there.

The formula is "working" with no errors but it is not pulling the correct counts between the date ranges. So my thought is that I don't have something typed correctly in this portion of the formula:

{BTPMASTER 2024 Range 4}, <TODAY(+60), {BTP MASTER 2024 Range 4}, >TODAY(+30)

Thanks,

Nick

Carson Penticuff

{BTPMASTER 2024 Range 4}

{BTP MASTER 2024 Range 4}

You have both of those references in your formula, I had assumed one was not correct.

Nrob14

Hi Carson,

It's referencing the same column that has the created date in it. So I don't think that's the problem but I could be wrong.

Thanks,

Nick

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.