Nested IF/CountIF Help

KL Jones

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!

SSFeatures

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

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

dojones

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.

KL Jones

https://community.smartsheet.com/discussion/comment/454714#Comment_454714

This worked!! Thank you so much!