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!