Using CountIfs to track a column will multiple options

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

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:


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

    thinkspi.com

Answers

  • Emily Zeiger
    Emily Zeiger ✭✭✭✭✭✭

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:


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

    thinkspi.com

  • 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
    Paul Newcome ✭✭✭✭✭✭

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


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

    thinkspi.com