Problem with Health Column Summary Formula

Hi all,

I have a formula counting a task's health based on status. I am now trying to summarize those results in a summary task@row using this formula

=IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray")))

However, with a row of unstarted tasks (grey) I get a red summary. Can someone see the error in my formula?

Screen Shot 2021-06-30 at 11.52.34 AM.png

Find more posts tagged with

Accepted answers

Todd Smelser

I believe I have it now.

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Gray"), "Gray", IF(AND(COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Green"), COUNTIF(CHILDREN(), "Gray") >= COUNTIF(CHILDREN(), "Yellow")), "Gray", IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray")))))

Seems to work perfectly

All comments

Mark Cronk

Hi @TSmelser ,

You need to add a COUNTIF(children(), "grey"). If the is 1 non-grey row do you want the summary to be the non-grey color? If so, add as you first function:

IF(COUNT(Children())=countif(Children(), "grey", "grey")

Mark

Todd Smelser

Hi Mark,

I have been trying to work with this as the first function

IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "grey", "grey"), IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray"))))

But this gives me "INCORRECT ARGUMENT"

Todd Smelser

Hi @Mark Cronk ,

I realized I should have tagged you in the response above.

Mark Cronk

Sorry for the delay in responding. Try:

IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "grey"), "grey", IF(AND(COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Red") >= COUNTIF(CHILDREN(), "Green")), "Red", IF(AND(COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Red"), COUNTIF(CHILDREN(), "Yellow") >= COUNTIF(CHILDREN(), "Green")), "Yellow", IF(AND(COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Yellow"), COUNTIF(CHILDREN(), "Green") >= COUNTIF(CHILDREN(), "Red")), "Green", "Gray"))))

Work?

Mark

Todd Smelser

Hi,

Yes, it seems to, though if the tasks beneath the summary are all grey, the status still goes red, instead of grey. There must be a ">=" parameter that is overriding the syntax you had me add

Todd Smelser

I believe I have it now.

Seems to work perfectly

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.