# Past Due Formula via Date Column in references source sheet

Options
✭✭✭

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.

Options

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

• ✭✭✭✭✭✭
Options

Hi @Ty Wickliffe

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

bassam.khalil2009@gmail.com

Options

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

• ✭✭✭
Options

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

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