Sheet Summary - SUM Chilldren Rows based on Status and Dates

08/07/20
Accepted

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!

Popular Tags:

Best Answers

  • L@123[email protected] ✭✭✭✭✭
    Accepted 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[email protected] ✭✭✭✭✭
    Accepted 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

Sign In or Register to comment.