I have a column called Systems. This allows users to thick multiple checkboxes example "Lighting","CCTV","Intruder"....

I would like to issue reports using the COUNTIF formula if the value of Systems has for example "Lighting" ticked in the checkbox. When using COUNTIF it will only count the row if "Lighting" is the only system checked. If multiple systems are checked apart from Lighting" COUNTIF will not add the row.

I think I have to use a combination of COUNTIF and CONTAINS but I am not managing to make it work.

This is the syntax that I am using:

=COUNTIF([Systems]1:[Systems]5, "Lighting")

Best Answer


  • Katie Seaburg
    Katie Seaburg ✭✭
    edited 10/23/20

    Follow up question - If "TV" was an option under systems how do you not count "TV" with "CCTV"? - Never mind - I think I just had the order wrong...

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!