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
-
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]#
-
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
-
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,
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!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 417 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!