Rollup Formula

Genevieve P.

Good morning, @CJU !

Can you copy/paste the exact formula you're using for the Not Applicable rollup field?

ex:

=COUNTIFS(Status:Status, "Not Applicable", [Category]:[Category], OR(@cell = "", @cell <> "PARENT"))

If the text in quotes is even one character different from what's in your sheet you'll receive a Count of 0. Is it possible there's a small typo?

Cheers,

Genevieve

CJU

Hi

Thanks. You have been great. I can't believe I missed it, I had "No Applicable". Fixed now and working.

You have been really helpful. Thank you very much.

Genevieve P.

No problem at all! I'm glad that we sorted it all out. 🙂

CJU

Hi Genevie

If I have several tasks and 1 of those is Not Applicable, the Roll Up shows "No Started"

If there are a number of tasks, as soon as some are In progress, the Roll Up should say In Progress. If all are Complete. even if 1 is Not Applicable. the roll-up should state Complete.

If all are Not Started - roll-up is Not Started

If at least 1 In Progress - roll-up is In Progress

If all are Not Applicable - roll-up is Not Applicable

How do I modify our formula accordingly:

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Applicable"), "Not Applicable", IF(OR(CONTAINS("In Progress", CHILDREN()), AND(COUNTIF(CHILDREN(), "Complete") > 0, COUNTIF(CHILDREN(), "Not Started") > 0)), "In Progress", "Not Started")))

CJU

Hi

Are you able to take a look at this question please?

Craig

Genevieve P.

Hi @CJU

Good catch! Our first statement counts the number of children and sees if they all say Complete, but of course you may have some that are Not Applicable so they should be ignored.

Try this:

=IF(COUNTIF(CHILDREN(), <> "Not Applicable") = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Applicable"), "Not Applicable", IF(COUNTIF(CHILDREN(), <> "Not Applicable") = COUNTIF(CHILDREN(), "Not Started"), "Not Started", "In Progress")))

Instead of identifying all the possible "In Progress" options, I have it as the default if there's any combination other than the totals (ex. if they're all Complete it's Complete, if they're all N/A, it's N/A, if they're all Not Started, it's Not Started... otherwise it's In Progress).

Does that sort out all of your potential options?

Cheers,

Genevieve

CJU

Hi

Its not quite right on 1 account

All "In Progress" - roll up "In Progress" - correct

All "Not Started" - roll up " Not Started" - correct

All "Complete" - roll up "Complete" - correct

All "Not Applicable" - roll up "Complete" - incorrect; should be "Not Applicable"

At least 1 "In Progress", even with others "Not Applicable" or other - roll up "In Progress" - correct

Genevieve P.

Of course! The order of operations is really important. We just need to change this around.

Put the "Not Applicable" first.

=IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Not Applicable"), "Not Applicable", IF(COUNTIF(CHILDREN(), <> "Not Applicable") = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNTIF(CHILDREN(), <> "Not Applicable") = COUNTIF(CHILDREN(), "Not Started"), "Not Started", "In Progress")))

CJU

Thank you

Genevieve P.

No problem! I'm glad we could find the right combination of instructions for you.

CJU

You are really great. Thanks

JDI

I'm working on a similar problem. After indenting/outdenting, my rollup isn't keeping track of if a task is/was a parent and calculating correctly.

I've created a new column that I can hopefully apply after any project manipulations. I want it to simply look at the row to determine if this is a parent task (checkbox is checked or not), and then add child tasks from the "Budget" column.

Here's what I have, but it's unparseable:

=if(isParent),true,SUM(CHILDREN(Budget),"")))

Genevieve P.

Hi @JDI

You'll need to have a row reference with each of your column references. In this case, if the formula is placed in the Parent row, then you'll use @row to indicate the formula should look in the same row for your instructions.

Try:

=IF(isParent@row = true, SUM(CHILDREN(Budget@row)), "")

If this doesn't work, it would be useful to see a screen capture of your sheet with the columns showing, but please block out sensitive data.

Cheers,

Genevieve