Formula to add up % complete

JennS_

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.

Dan W

@JennS_

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.

Kleerfyre

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")

JennS_

This did not work. It returned an #UNPARSEABLE

Dan W

@JennS_

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.

JennS_

Thanks! This worked.

Sarita Sah

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

Andrée Starå

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!

Sarita Sah

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

Sarita Sah

@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å

@Sarita Sah

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!