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.
Best Answer

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.
Answers

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&O10%
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))

Hi @Sarita Sah
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.

@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
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.4K Get Help
 364 Global Discussions
 200 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 137 Brandfolder
 129 Just for fun
 128 Community Job Board
 446 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!