Using CountIfs to track a column will multiple options

Earl P · 04/11/22

Hi everyone I have a multi select column that I am trying to use a countifs statement to count of instances of a name. However it only returns cells where only the name exist and nothing else. Does anyone know how to make it that it counts all instances? Formula below for example

=COUNTIFS({Training Project Task List Range 3}, "Aubrey Campbell", {Late Check}, @cell = "Late")

Paul Newcome · 04/11/22

Try this:

=COUNTIFS({Training Project Task List Range 3}, HAS(@cell, "Aubrey Campbell"), {Late Check}, @cell = "Late")

Emily Zeiger · 04/11/22

For multiselect columns you have to include a contains formula in there. =COUNTIFS(CONTAINS("Aubrey Campbell", {Training Project Task List Range 3}), {Late Check}, @cell = "Late")

Paul Newcome · 04/11/22

Try this:

=COUNTIFS({Training Project Task List Range 3}, HAS(@cell, "Aubrey Campbell"), {Late Check}, @cell = "Late")

Earl P · 04/21/22

Thank you all the suggestions worked very well!

I have another question, I would like to look for the average duration of rows which fit specific conditions. How would I begin creating a formula for this?

Paul Newcome · 04/21/22

You would use AVG(COLLECT(.........)

=AVG(COLLECT({range to average}, {1st criteria range}, 1st criteria, {2nd criteria range}, 2nd criteria, ............................))

Using CountIfs to track a column will multiple options

Best Answer

Answers

Help Article Resources

Categories