Hi I am trying to create a formula to give me a percentage complete in a sheet summary

Gary Collins

Below is what I am using:

=(COUNTIF(CABLE:CABLE, 1)) / COUNTIFS([TURN OVER]:[TURN OVER], "ECORE") this formula works and returns a % in my sheet summary that looks correct.

When trying to get results from a different "turn over" I get incorrect results

=(COUNTIF(CABLE:CABLE, 1)) / COUNTIFS([TURN OVER]:[TURN OVER], "AB")

I am using checkboxes to collect information in the cable column, but with no boxes checked for cable that are in turnover "AB" I get 28% as result in sheet summary.

Zachary Hall

Hi @Gary Collins,

These formulas as written are returning the count of ALL boxes that are checked in the numerator regardless of if they meet the denominator requirement or not. In order to calculate these correctly the formulas should look like:

• =(COUNTIFS(CABLE:CABLE, 1, [TURN OVER]:[TURN OVER], "ECORE")) / COUNTIFS([TURN OVER]:[TURN OVER], "ECORE")
• =(COUNTIFS(CABLE:CABLE, 1, [TURN OVER]:[TURN OVER], "AB")) / COUNTIFS([TURN OVER]:[TURN OVER], "AB")

Hope this helps!

Zachary Hall

Hi @Gary Collins,

These formulas as written are returning the count of ALL boxes that are checked in the numerator regardless of if they meet the denominator requirement or not. In order to calculate these correctly the formulas should look like:

• =(COUNTIFS(CABLE:CABLE, 1, [TURN OVER]:[TURN OVER], "ECORE")) / COUNTIFS([TURN OVER]:[TURN OVER], "ECORE")
• =(COUNTIFS(CABLE:CABLE, 1, [TURN OVER]:[TURN OVER], "AB")) / COUNTIFS([TURN OVER]:[TURN OVER], "AB")

Hope this helps!

Gary Collins

THANKS ZACH 😀