# Formula to add up % complete

✭✭✭

I am trying to create a formula to count the number of 100% complete items in the % complete column AND the Category is the item on the Parent row (in the example below it would be "AUS").

I tried:

=COUNTIF({Sheet -% Complete} = 1, AND(COUNTIF({Sheet - Category}, "AUS")))

I get an error of INVALID OPERATION.

Thanks for the help.

• ✭✭✭✭✭

Are you referencing another sheet? If so using the reference you have in your formula you would put

=COUNTIFS({Sheet -% Complete}, 1,{Sheet - Category}, "AUS")

This is also assuming you have the format for the Percentage set to percentage.

• ✭✭✭✭✭✭

You need to use a COUNTIFS Statement. You will also have to put this formula in a different cell in a different column as the formula will error out as it won't like trying to count itself.

=COUNTIFS([% Complete]:[% Complete], "100%", Category:Category,"AUS")

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭✭

This did not work. It returned an #UNPARSEABLE

• ✭✭✭✭✭

Are you referencing another sheet? If so using the reference you have in your formula you would put

=COUNTIFS({Sheet -% Complete}, 1,{Sheet - Category}, "AUS")

This is also assuming you have the format for the Percentage set to percentage.

• ✭✭✭

Thanks! This worked.

• Can someone help me understand why this formula is not working?

I have given weightage to 6 items - BAU:30%, Escalation - 10%, IAT Implementation - 10%, UAT - 30%, IAT Renewal -10% & E&O-10%

Below formula giving me the final result of 50% instead of 100% when all items have 100 score. But when I reduce individual item score below 100, Final result is also reducing correctly.

=IF([BAU Score]@row = 1, 0.3, IF([BAU Score]@row >= 0.5, 0.15, 0)) + IF([Escalation Score]@row = 1, 0.1, IF([Escalation Score]@row >= 0.5, 0.05, 0)) + IF([IAT Implementation Score]@row = 1, 0.1, IF([IAT Implementation Score]@row >= 0.5, 0.05, 0)) + IF([UAT Score]@row = 1, 0.3, IF([UAT Score]@row >= 0.5, 0.15, 0)) + IF([IAT Renewal Score]@row = 1, 0.1, IF([IAT Renewal Score]@row >= 0.5, 0.05, 0)) + IF([E&O Score]@row = 1, 0.1, IF([E&O Score]@row >= 0.5, 0.05, 0))

• ✭✭✭✭✭✭

I hope you're well and safe!

I added the formula to an example sheet, which worked for me.

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• edited 06/26/23

@Andrée Starå, hey I realized the problem, thanks a lot. I had missed to add "greater than" along with =, so its working now. thanks anyways.

• @Andrée Starå, hey I realized the problem, thanks a lot. I had missed to add "greater than" along with =, so its working now. thanks anyways.

• ✭✭✭✭✭✭

Excellent! Easy to miss!

Happy to help!

Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

SMARTSHEET EXPERT CONSULTANT & PARTNER

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!