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