Help getting the summary of my sheet

bsaucedo

I am not getting the result I need from a formula I am using. I am trying to track how many audits are completed and how many are left to do in total for this quarter '__ of ___ done', currently I am using this formula:

=COUNTIF(CHILDREN(Done2:Done130), 1) + " of " + COUNT(CHILDREN([Task Name]2:[Task Name]130)) + "" + " Done"

The hierarchy looks for example:

-Spring 2024

--Location

---Department

----Sub department (sometimes this doesn't exist)

Some sections have only a location and department and some have all three. I would like the formula to count the 'done' items -- for example it should count the the sub department not the department (if there is one), but currently it is counting everything (location , department, and sub department) making it look like we have 124 audits when we actually only have 100. Any help would be beneficial.

James Keuning

Create a column that puts something in it, like the number 1 for example, for each thing that you want to count, rather than counting all children.

And then your formula can count rows that have 1 in that column. This way you can also use Done:Done instead of using the specific row numbers.

bsaucedo

Do you know a formula that can be used to get the done column and the new extra column with the value I am trying to count?

Kelly Moore

Hey @bsaucedo

The COUNTIF function only allows one criteria. The COUNTIFS (plural) allows multiple criteria (and can also be used with only one criteria). I'm assuming you followed @James Keuning 's advice and found or added another column to target the rows that should be counted (or those that should be ignored). For example, sometime the Hierarchy Level becomes important when using a multi-leveled sheet like yours. Edit the formula below with the name of the column (and criteria if different than 1)

=COUNTIFS(CHILDREN(Done:Done), 1, [New Helper]:[New Helper],1) + " of " + VALUE(COUNTIFS(CHILDREN([New Helper]:[New Helper]),1)) + "" + " Done"

Does this work for you?

Kelly

bsaucedo

Hi @Kelly Moore,

I did follow James Keuning's suggestion. I inputted the formula you provided and got the incorrect argument error. Here is a screen grab of my sheet for you to see. I am not sure what adjustments I need to make.

Kelly Moore

hey @bsaucedo

My bad. I forgot to add the CHILDREN before your new column name. The range element must all be equal amongst all the terms.

=COUNTIFS(CHILDREN(Done:Done), 1, CHILDREN([New Helper]:[New Helper]),1) + " of " + VALUE(COUNTIFS(CHILDREN([New Helper]:[New Helper]),1)) + "" + " Done"

Kelly

bsaucedo

That worked! @Kelly Moore Do you know how I can get the percentage of completed? (row 2 to row 129) have values I would like to average. Currently I have:

=AVG(CHILDREN([Percent Complete]@row)) for each child as you can see for the dark blue and light blue sections but would like it for the overall audit sheet(green). I believe that is looking at the whole row not just the targeted areas correct?

Kelly Moore

@bsaucedo

In the 'white' child rows, how is that %complete inputted? Manually or by formula?

bsaucedo

@Kelly Moore I have them in as a dropdown but also there is an automation depending on the status of each row. Green - 100%, Yellow - 50%, Red - 0%.

Kelly Moore

@bsaucedo

The short answer to your question is yes, we could roll up your %complete to the Parent rows, and your Green row is the Parent to the sheet. Your opportunity comes from how you are inputting data into the Child rows. Unless you manually enter a formula into every parent row (and I don't recommend that at all unless your sheet is never going to change or grow), you can't have formulas and manual input in the same column.

You could add a second %complete column and use that as the roll up. Formulas would be in that column. Or, as a different option, you could abandon manually selecting values from the dropdown column and we would build a formula for the child rows that replicate what the automation is doing. We would use IF statements to determine if the row was a Parent row or not, and calculate the formula accordingly. You would not need a second %complete column for this - although I might suggest a different helper column to simplify the formulas a bit. In my sheets, wherever possible, I use the second option so that %completes are always calculated rather than giving people choices. Anytime you are dealing with %complete there are trade-offs no matter what approach you select.

To summarize, I gave you 3 options

• Manually insert a formula into every Parent row. (This is the least reliable solution. It's not recommended)
• Add a second %complete column to either use for the manual entries or the parent row roll up.
• Use formulas only for every row. (This is what I recommend if your users will allow formula calculated %complete for their child rows)

Kelly

bsaucedo

Okay thank you!

Kelly Moore

@bsaucedo

Once you decide which option is best for you, let me know and I'll help you build it

Kelly