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!