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!

Tags:

Best Answer

  • SSFeatures
    SSFeatures ✭✭✭
    edited 09/19/24 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

  • SSFeatures
    SSFeatures ✭✭✭
    edited 09/19/24 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

  • dojones
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!