How Can I Combine Multiple Percentages Into One Cumulative Percentage?

I'm working on a project that has multiple components connected to the overall goal. To better know where we're at, I've created a done category to track progress in a percentage based on when a new box is checked. To accomplish this, I used the following formula:
=ROUND(COUNTIF(Done3:Done8, 1) / (COUNTIFS(Done3:Done8, 0) + COUNTIFS(Done3:Done8, 1)), 2) * 100 + "%"

This has left me with with four separate percentages that I'd like to combine into one for the overall project. What formula can I use to accomplish this? The combined percentage would live in the black space at the top.

Find more posts tagged with

Sheets

Formulas

functionality

Accepted answers

All comments

Adrien Leduc

Use the indentation so that the top row is parent to your 4 percentages rows and use a formula such as =COUNTIF(DESCENDANT(),1)/(COUNTIF(DESCENDANT()1)+COUNTIF(DESCENDANT(),0))

But before that, you'll need to modify your % rows because the way you wrote your formula probably transformed it into text. Instead use =COUNTIF(Done3:Done8, 1) / (COUNTIFS(Done3:Done8, 0) + COUNTIFS(Done3:Done8, 1))

Then use the formatting options to change the result from 0.33 (and the others) to a percentage and move the decimal option. Do the same for the top row.

If you use your indentation correctly you could also replace your % rows formula by =COUNTIF(CHILDREN(), 1) / (COUNTIFS(CHILDREN(), 0) + COUNTIFS(CHILDREN(), 1)) so it's more scalable.

DerekLien

Adrien -

I have the indentation set accordingly so my top row is the parent of my percentage rows. When entering the last formula you shared, the response I'm getting is #DIVIDE BY ZERO.

DerekLien

Also, when I submit the second formula for my percentage cells, it's giving me the error #BOOLEAN EXPECTED

Adrien Leduc

Could you please share a screenshot again to check your indentation? As I can see in the original most of it is wrong I just want to double check.

I did make mistakes in my formulas, add a S to all the DESCENDANTS and COUNTIFS. I also forgot a comma so the first one would be:

=COUNTIFS(DESCENDANT(),1)/(COUNTIFS(DESCENDANTS(),1)+COUNTIFS(DESCENDANTS(),0))

And the last one:

=COUNTIFS(CHILDREN(), 1) / (COUNTIFS(CHILDREN(), 0) + COUNTIFS(CHILDREN(), 1))

Do send a screenshot of the sheet with the errors if you see some again.

DerekLien

Screenshot 2026-03-19 at 9.38.07 AM.png

Screenshot 2026-03-19 at 9.37.47 AM.png

This is the error I'm getting, perhaps I'm not understanding something correctly .

Adrien Leduc

Thanks I see, that's my bad, the column is expecting a checkbox (1 or 0) so it's not happy it's getting another kind of number. Your *100 + % is actually a good trick for this case, but I don't know how it's going to react to the text values. We can give it a shot with

=COUNTIFS(DESCENDANT(),1)/(COUNTIFS(DESCENDANTS(),1)+COUNTIFS(DESCENDANTS(),0)) * 100 + "%"

=COUNTIFS(CHILDREN(), 1) / (COUNTIFS(CHILDREN(), 0) + COUNTIFS(CHILDREN(), 1)) * 100 + "%"

Otherwise you could use a helper column where you would put the formulas without * 100 + "%" (keep the new column as text/number). Add the references to your original column: Done@row inside the children and descendants.

Adrien Leduc

Now that I think about it, can you check the column properties and look if the box "restrict to checkbox use only" is checked? if it is, try unchecking it and see if the formula works.

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.