Sheet Summary - SUM Chilldren Rows based on Status and Dates

Good Afternoon!

I am trying to sum children rows where certain status' exist where the Pick-Up Date is today, within the next two days, and in the future beyond that. (Really, 3 formulas).

I tried applying the following formula before involving dates to make sure the first part of logic is correct but it is giving me an #INCORRECT ARGUMENT SET. My Formula: =SUMIFS(Group:Group, "Dan Billingsley", Status:Status, CONTAINS("Open", @cell)).

Let me know your thoughts. Thank you!

Best Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    give the formulas below a try


    Today

    =countifs(children([pick-up date]2),@cell = today(),children(status2),"Open")

    Next 2 Days

    =countifs(children([pick-up date]2),@cell <= today()+2,children(status2),"Open") - Today#

    Future

    =countif(children(status2),"Open") - [Next 2 Days]#

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    countifs has an unlimited number of criteria, you can just keep stacking them. If you have multiple criteria on a single range you can use the formulas AND and OR, and to retrieve a false case you can use NOT


    pickup date is today and received date is in the past

    =countifs(children([pick-up date]2),@cell = today(),children(status2),"Open",children([recieved date]2),<today())


    more statuses

    =countifs(children([pick-up date]2),@cell = today(),children(status2),or(@cell ="Open",@cell = "Covered"))


    These can both be built on in any direction you want.

Answers

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    give the formulas below a try


    Today

    =countifs(children([pick-up date]2),@cell = today(),children(status2),"Open")

    Next 2 Days

    =countifs(children([pick-up date]2),@cell <= today()+2,children(status2),"Open") - Today#

    Future

    =countif(children(status2),"Open") - [Next 2 Days]#

  • Davis Dye
    Davis Dye
    edited 08/07/20

    L@123,

    Thank you for your input! These work perfectly!

    With those formulas being established now, how could I add:

    1) another date factor (ex. pick-up date is today and received date is in the past) to any of the expressions above?, and

    2) how could I add more status's to any of the expressions above (ex. Open and Covered)?

    Let me know your thoughts when you have a second. Thank you!

  • L_123
    L_123 ✭✭✭✭✭✭
    Answer ✓

    countifs has an unlimited number of criteria, you can just keep stacking them. If you have multiple criteria on a single range you can use the formulas AND and OR, and to retrieve a false case you can use NOT


    pickup date is today and received date is in the past

    =countifs(children([pick-up date]2),@cell = today(),children(status2),"Open",children([recieved date]2),<today())


    more statuses

    =countifs(children([pick-up date]2),@cell = today(),children(status2),or(@cell ="Open",@cell = "Covered"))


    These can both be built on in any direction you want.

  • L@123,

    These answer all of my questions perfectly! Thank you so much! Have a great weekend!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!