Hi I am trying to create a formula to give me a percentage complete in a sheet summary
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.
Best Answer

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!
Best,
Zach Hall
Training Delivery Manager / Charter Communications
Answers

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!
Best,
Zach Hall
Training Delivery Manager / Charter Communications

THANKS ZACH 😀
Help Article Resources
Categories
Check out the Formula Handbook template!