Nested IF/CountIF Help
Hi! I am currently building a dashboard to show reporting on employee injuries for cases that are not resolved. I am having trouble with my formula and have been spinning my wheels. I need the formula to look at my reference data and, if the checkbox in the 'Done' column is not marked, then return the incident type into my data sheet.
I currently have this set up for the number of all cases by incident type, and the formula is successfully working. See below:
The formula for the above is =COUNTIF({Incident Type}, [Column5]@row).
Here are the options I've tried so far for the updated table, to no avail:
=IF(COUNTIF({Test Reference}, [Column5]@row), [Done]@row=0)
=IF(COUNTIF({Test Reference}, [Done]@row=0), [Column5]@row)
For context, I did have to make a new range (Test Reference) as the initial one (Incident Type) did not include the column with the checkbox in it.
Any help is greatly appreciated!
Best Answer

Hi,
Try using the COUNTIFS function. This function lets you add as many conditions as you need, so you can expand it to count with 1 or more if statements attached to it.
For your case, I think this formula is what you want:
=COUNTIFS({Incident Range}, Incident@row, {Checkbox Range}, 0)
This Counts all of the incidents in the incidents range so long as the incident name matches the incident in the current row, such as "Car Accident". Then, it looks at the checkbox for that row and checks that it is 0, and then counts the row.
Hope this helps!
SSFeatures
SSFeatures  The browser extension that adds more features into SmartSheet.
 Automatic sorting, sorting with filters, saving sort settings
 Spell checking
 Report PDF generator that supports grouped and summarized reports
Answers

Hi,
Try using the COUNTIFS function. This function lets you add as many conditions as you need, so you can expand it to count with 1 or more if statements attached to it.
For your case, I think this formula is what you want:
=COUNTIFS({Incident Range}, Incident@row, {Checkbox Range}, 0)
This Counts all of the incidents in the incidents range so long as the incident name matches the incident in the current row, such as "Car Accident". Then, it looks at the checkbox for that row and checks that it is 0, and then counts the row.
Hope this helps!
SSFeatures
SSFeatures  The browser extension that adds more features into SmartSheet.
 Automatic sorting, sorting with filters, saving sort settings
 Spell checking
 Report PDF generator that supports grouped and summarized reports

Try this:
IF({Done}=0, COUNTIF({Incident Type}, [Column5]@row))
Reference Done to the sheet that it is in. Done is a full column in another sheet like Incident Type. Name it to what you want.

This worked!! Thank you so much!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 379 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 304 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!