Past Due Formula via Date Column in references source sheet

Hello,

I have the below formula to show total count of projects that are less than or equal to today days and the due date.

=COUNTIFS({Estimator}, [Assigned To]@row, {Assigned To}, [Assigned To]@row, {Quote Due Date}, <=TODAY(), {Quote Sent Date}, "")

How can this be tweak to show, Total Count Projects Past Their Due Date?

The Quote due date is coming in from another source sheet that is a date column?

Feels like I have been staring at the symbol actions (eg <= and >= ) for ages and now I am starting to get confused what they actually mean with what I am trying to do lol.

Find more posts tagged with

Accepted answers

Genevieve P.

Hi @Ty Wickliffe

Are you just looking to remove out any of the "assigned to" criteria so it shows the totals?

COUNTIFS formulas are great because of their repetitive structure. In this instance all you need to do is remove out the first two ranges and criteria so you're only looking at the due date (and if the Sent Date is blank).

Try this:

=COUNTIFS({Quote Due Date}, <=TODAY(), {Quote Sent Date}, "")

This count will include TODAY's date because of the = sign, so if you want to only have dates in the past you can do this:

=COUNTIFS({Quote Due Date}, <TODAY(), {Quote Sent Date}, "")

This says, count the cells in the range "Quote Due Date" that are less than Today (or in the past of Today), but only if the Quote Sent Date is blank.

Let me know if this is what you were looking to do!

Cheers,

Genevieve

All comments

Bassam Khalil

Hi @Ty Wickliffe

Hope you are fine, could you please supply a screenshot for your sheets (Delete/replace any confidential/sensitive information before sharing)

Genevieve P.

Hi @Ty Wickliffe

Are you just looking to remove out any of the "assigned to" criteria so it shows the totals?

Try this:

=COUNTIFS({Quote Due Date}, <=TODAY(), {Quote Sent Date}, "")

This count will include TODAY's date because of the = sign, so if you want to only have dates in the past you can do this:

=COUNTIFS({Quote Due Date}, <TODAY(), {Quote Sent Date}, "")

This says, count the cells in the range "Quote Due Date" that are less than Today (or in the past of Today), but only if the Quote Sent Date is blank.

Let me know if this is what you were looking to do!

Cheers,

Genevieve

Ty Wickliffe

Hi @Bassam.M Khalil ,

Thanks for reaching out so quick. @Genevieve P managed to answer my question.

@Genevieve P - your formula worked a treat. Thanks again! 😁

Genevieve P.

No problem! Glad 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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.