Counting parent 'in progress' rows

Hi, I'm trying to write a formula to count the number of tasks In progress, where the row level is"0" or "0+" (which are parent rows). Here is my formula:

=COUNTIFS({Level}, OR(@cell = "0", @cell ="0+"), {Status}, "In progress")

Here's my sheet:

I should be getting a total count of 5, but the formula is returning a count of 3 and I'm not sure why?

I'd also like to do another formula and count the number of "In progress" 0 or 0+ rows by team, but I'm not sure where to insert the Team# into the formula?

Find more posts tagged with

Formulas

Hierarchy

Accepted answers

All comments

Paul Newcome

Try this as a column formula in the Level column:

=COUNT(ANCESTORS([Task Name]@row)) + IF(COUNT(CHILDREN([Task name]@row)) > 0, "+", "")

kmiras

Thanks @Paul Newcome - Yes, I'm currently using that formula for the Level column.

What I'm referring to is a separate data sheet that I'm using for various formulas in order to pull data to add to a dashboard. I'm trying to calculate parent rows (0 and 0+ rows) where the status is still 'in progress', and status is 'completed'. I'd also like to calculate parent rows 'in progress' but by team. Below is my separate data sheet to clarify. The counts seem off - looks like it's only counting 0+ rows and not counting and including the 0 rows, and I'm not sure why?

Apologies for confusing or trying to complicate things! ;)

Paul Newcome

Right. I understand the final COUNTIFS is on a separate sheet. The formula I suggested is to standardize the data types in the Level column of the source data.

If a column contains both numbers and text strings 0 and "0+", it can throw formula off that are referencing them such as in your case with the COUNTIFS.

The formula I provided should output ALL text even if it looks like numbers. Since they are all text, you should be at least getting your base COUNTIFS correct.

Are you able to provide a screenshot of the formula in your Level column open in the sheet as if you are editing it?

kmiras

Here is a screenshot and I'm noticing I'm missing the last "" in the level formula - maybe that's what you're referring to and why I could be throwing the formula

Paul Newcome

Yes. That will make the difference. If you "add" quotes to the end of a number, it will convert it to a text string. Since you do not have that at the end, you are outputting just the number and then only converting to text when you add the "+". Add that last portion tot he IF statement and see if that helps.

Then… To get it by the team, you would just need to add another range/criteria set to the COUNTIFS the same way you have a range/criteria set for the level and a range/criteria set for the status. The new range would be your [Team(H)] column, and the criteria would be the team.

kmiras

OK, thank you so much! Let me work on the team formula and see if I can figure it out. Thanks again!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.