COUNTIFS FOR MULTIPLE CRITERIA INCLUDING CHECKBOX

Katrina Savage

Hi

Need a COUNTIF (or COUNTIFS) formula that can count number of times a value appears in a column AND when checkbox appears on same row as the value.

have previously counted using just the number of times a value appears but now need to add the checkbox element.

Formula that has been working when a simple count:

=COUNTIF({2021 EMEA MED ED ACTIVITIES SCHEDULE Range 1}, CONTAINS(SPEAKER@row, @cell))

Have tried a few different variations/additions on this formula but having #UNPARSEABLE returned - e.g. this one did not work =COUNTIFS({2021 EMEA MED ED ACTIVITIES SCHEDULE Range 1} AND({2021 EMEA MED ED ACTIVITIES SCHEDULE Range 2},1} CONTAINS(SPEAKER@row, @cell))

This is my utilisation count sheet - VALUES in SPEAKER match the range search from source sheet

This is my source sheet - values in SPEAKER NAME are a dropdown list. The same dropdown list is inputted in speaker column in UTILISATION COUNT SHEET

Up until now was able to count number of times a name appears in SPEAKER NAME column but now want to count ONLY when CHECKBOX in 'CONTRACTED DIRECTLY' is checked.

Any ideas on how to do this?

David Tutwiler

COUNTIFS contains an AND function by default. So instead of adding an AND, you can just put your second criteria range and condition into the formula. Depending on how you have your ranges set up, something like this should work:

=COUNTIFS({2021 EMEA MED ED ACTIVITIES SCHEDULE Range 1}, CONTAINS(SPEAKER@row, @cell), {2021 EMEA MED ED ACTIVITIES SCHEDULE Range 2}, true)