Help getting the summary of my sheet
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.
Answers

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.

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?

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 multileveled 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

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.

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

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?

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

@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%.

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 tradeoffs 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

Okay thank you!

Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!