Checking multiple columns if true then return a value by doing this calculation
Hello,
I am having trouble with this formula. Your input is very much appreciated.
I have two columns I am checking if situation is true and if true, then i want to calculate the %complete against all tasks. Here are my columns
my current formula:
=IF(AND([Compliance Validation]@row = true, [% Complete]@row = "Complete"), COUNTIF([% Complete]:[% Complete], "Complete") / COUNT(Standard:Standard), 0)
Output I receive is a value of 20.69% which is not entirely correct.
Because the ask is to compute overall %complete if the Compliance Validation column is checked AND %Complete column is marked Complete but looks like the formula is only checking %Complete column for Complete and dismissing if the check box is checked also. Formula should not add the rows that are not checked into calculating overall % complete of the tasks (standard).
I tested by unchecking the check boxes, and I still receive same value 20.69%. See below.
Appreciate any help on my formula.
Best Answer
-
Hi @Ari_Ari,
See if this gets you what you are looking for.
=IF(AND([Compliance Validation]@row = true, [% Complete]@row = "Complete"), COUNTIFS([% Complete]:[% Complete], "Complete", [Compliance Validation]:[Compliance Validation], true) / COUNT(Standard:Standard), 0)
Hope this helps,
Dave
Answers
-
Hi @Ari_Ari,
See if this gets you what you are looking for.
=IF(AND([Compliance Validation]@row = true, [% Complete]@row = "Complete"), COUNTIFS([% Complete]:[% Complete], "Complete", [Compliance Validation]:[Compliance Validation], true) / COUNT(Standard:Standard), 0)
Hope this helps,
Dave
-
@DKazatsky2 Thank you so much! This works just like it should. I see you used CountIFS and added the checkbox condition again.
Thank you.
-
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!