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!

Find more posts tagged with

Formulas

Sheet Summary

Accepted answers

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.

All comments

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.