Formula to report total tasks and total tasks completed

Brandon Mitchell

Hi, I'm looking for some help with the below.

For the most part I'm able to make the formula work, however I'm unsure how to account for Friday. For example today is Monday, but the formula only reports Sundays data (nonworkday). Also all of my formulas are in the Sheet Summary, as I'm trying to prevent any need for additional columns if possible.

Total Tasks:

=COUNTIFS([Due Date]:[Due Date], =TODAY(-1), [Critical Ops.]:[Critical Ops.], ="Yes")

Total Completed Tasks:

=COUNTIFS([Due Date]:[Due Date], =TODAY(-1), Status:Status, ="Yes", [Critical Ops.]:[Critical Ops.], "Yes")

Edit To add the populated number from the formula would then populate into a dashboard, so it would need to be automatic instead of specific to individual rows/cells and manually inputting the information in.

Genevieve P.

Hi @Brandon Mitchell

Would you need to account for if Today is Sunday at all (to look back 2 days to Friday), or do you just need it to be assuming Today is Mon-Fri?

The way I would personally figure this out would be to use an IF statement. This way you don't need a helper weekday column at all.

The IF statement would go, If Today is Monday, then do the calculation with TODAY(-3), otherwise, do the calculation with TODAY(-1)

Try this:

=IF(WEEKDAY(TODAY()) = 2, COUNTIFS([Due Date]:[Due Date], TODAY(-3), [Critical Ops.]:[Critical Ops.], "Yes"), COUNTIFS([Due Date]:[Due Date], TODAY(-1), [Critical Ops.]:[Critical Ops.], "Yes"))

Cheers,

Genevieve

Brandon Mitchell

UPDATE To hopefully make this more achievable, I added a Weekday column that correlates with the due date (i.e. IF Due Date = 02NOV2020, THEN Weekday = 2)

With that said I would need my formula to have an OR statement combining the below formulas

=COUNTIFS([Due Date]:[Due Date], =TODAY(-1), [Critical Ops.]:[Critical Ops.], ="Yes")

=COUNTIFS([Due Date]:[Due Date], =TODAY(-3), Weekday:Weekday, AND(@cell = 6), [Critical Ops.]:[Critical Ops.], ="Yes")

Both work as separate formulas, however when I try to combine them it either populates as 0 or gives me an error.

Genevieve P.

Hi @Brandon Mitchell

Would you need to account for if Today is Sunday at all (to look back 2 days to Friday), or do you just need it to be assuming Today is Mon-Fri?

The way I would personally figure this out would be to use an IF statement. This way you don't need a helper weekday column at all.

The IF statement would go, If Today is Monday, then do the calculation with TODAY(-3), otherwise, do the calculation with TODAY(-1)

Try this:

=IF(WEEKDAY(TODAY()) = 2, COUNTIFS([Due Date]:[Due Date], TODAY(-3), [Critical Ops.]:[Critical Ops.], "Yes"), COUNTIFS([Due Date]:[Due Date], TODAY(-1), [Critical Ops.]:[Critical Ops.], "Yes"))

Cheers,

Genevieve

Brandon Mitchell

My apologies for the delayed response, but this formula worked perfectly thank you so much

Genevieve P.

@Brandon Mitchell

No problem at all, I'm glad it worked for you! 🙂