Formula to report total tasks and total tasks completed

Brandon Mitchell
Brandon Mitchell ✭✭✭
edited 11/02/20 in Formulas and Functions

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.

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    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

Answers

  • 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.
    Genevieve P. Employee Admin
    Answer ✓

    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

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

  • Genevieve P.
    Genevieve P. Employee Admin

    @Brandon Mitchell

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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!