COUNTIF WITH MULTIPLE CHECKBOXES

Bernard Zammit

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")

Ramzi K

@Bernard Zammit

Try this formula: =COUNTIF(Systems:Systems, HAS(@cell, "Lighting"))

The example below shows how to use it to dynamically count all your systems in a list.

I hope this helps.

Cheers,

Ramzi

Ramzi K

@Bernard Zammit

Try this formula: =COUNTIF(Systems:Systems, HAS(@cell, "Lighting"))

The example below shows how to use it to dynamically count all your systems in a list.

I hope this helps.

Cheers,

Ramzi

Katie Seaburg

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