Sheet Summary - SUM Chilldren Rows based on Status and Dates

Davis Dye

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!

L_123

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

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

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

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

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.

Davis Dye

L@123,

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