COUNTIFS help for future dates

I wanted to create summary tasks for items that are past due and then items that are upcoming in the next 30 days.

My Past Due formula is working correctly:

=COUNTIFS([End Date]:[End Date], <TODAY(), [% Complete]:[% Complete], <0.1)

My Upcoming tasks formula is not:

=COUNTIFS([Start Date]:[Start Date], <=TODAY(+30), [% Complete]:[% Complete], <0.1)

It should be returning the same number that I see in the filter which is 108 rows, but it is returning 391 rows. I'm stumped

Find more posts tagged with

Formulas

Accepted answers

All comments

Jeff Reisman

@Jen S.

So your second formula is only supposed to count those with start dates in the next 30 days? If so, the criteria you're giving it counts everything in the next 30 days and everything in the past too. You need to add criteria excluding start dates in the past:

=COUNTIFS([Start Date]:[Start Date], <=TODAY(+30), [Start Date]:[Start Date] >= TODAY(), [% Complete]:[% Complete], <0.1)

Jen S.

@Jeff Reisman I'm okay with it still counting the past due tasks in the upcoming tasks because they still need to be counted. But you made me think about that logic.. :) On some further investigation, my COUNTIFS is also counting parent rows. Is there a way to just look at children? That would give me what I need.

Jeff Reisman

@Jen S. Can you share a screenshot of your data structure (blur out any sensitive info)? That might help me figure out how best to exclude the parent rows.

Jen S.

@Jeff Reisman This is an example of the data:

There would be similar tasks for the PMO meeting but I have them rolled up so you can get the idea.

Jeff Reisman

Taking your original Upcoming Tasks formula, if you put it on the Parent row (Conduct Core team Meetings... , Conduct Project Management Meetings...), this will only count the child rows of each parent row:

=COUNTIFS(CHILDREN([Start Date]@row), <=TODAY(+30), CHILDREN([% Complete]@row), <0.1)

Jen S.

@Jeff Reisman Here is an example of some of the data:

Jen S.

@Jeff Reisman

Here is my new syntax: =COUNTIFS(CHILDREN([Start Date]:[Start Date]), <=TODAY(+30), CHILDREN([% Complete]:[% Complete]) < 0.1)

Now I'm getting an invalid operation error

Jeff Reisman

@Jen S.

With the formula on the parent row, you don't need to give the formula the entire column range. Telling it CHILDREN([Start Date]@row) is giving it the range to evaluate, which is all the child rows of that parent row. (Also, missing a comma after the % Complete range.)

So if you put this

=COUNTIFS(CHILDREN([Start Date]@row), <=TODAY(+30), CHILDREN([% Complete]@row), <0.1)

on the parent row "Conduct Project Management Meetings (W..."

it will only consider the child rows under Conduct Project Management...

Do the same on the parent row for Conduct Core Team Meetings (Weekly - W...

Then you have three options if you want a roundup of all the subsections under "Project Administration". If you use the same formula as above, it will consider the data on the parent rows. If you want it to consider all rows (including the Parent rows,) change CHILDREN to DESCENDANTS. If you want it to ONLY count the child rows under each subsection, subtract the formula using CHILDREN from the formula using DESCENDANTS, as so:

=COUNTIFS(DESCENDANTS([Start Date]@row), <=TODAY(+30), DESCENDANTS([% Complete]@row), <0.1) - COUNTIFS(CHILDREN([Start Date]@row), <=TODAY(+30), CHILDREN([% Complete]@row), <0.1)

Jeff Reisman

@Jen S. Just to show you how this works in real life, this is from a test sheet of mine. You can see I have 4 hierarchy levels here. I'm working on Level 2 so that I have three levels, to match your setup. "Project Type 1" would correspond with your "Project Administration" row. I want to count all the cells in "HiddenColumn" that have a value greater than 2.

In this first screenshot I use CHILDREN and get a count of 1. This is because it's only considering the child rows of Project Type 1.

Next I use DESCENDANTS and get a count of 4. This is because it's considering ALL the descendant rows of Project Type 1, no matter what hierarchy level.

Finally, by subtracting the count of Child rows from the count of Descendant rows, we only get the count of grandchild rows.

Jen S.

@Jeff Reisman The total project is over 2000 lines. Will I need to do this for each parent/child area?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326193#Comment_326193

Really just on your parent rows, and only for the sections you want to track these counts on. If the sections are condensed down (child rows hidden) it's just a copy and paste onto each parent row. The way the formula is written (using @row) it will work on every parent row without changes.

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.