COUNTIF + CONTAINS - how do I incorporate them both?

nicoleejordannn

I'm trying to count how many times a value is used in a column. Sometimes the values are alone, sometimes there are multiple in a cell.

I got my COUNTIF formula down: =COUNTIF({Christoph Comms Tracker Audience Category}, "Select SMG Employees")

But I want to change it so it counts cells that include the value (along with other values).

So, if we're using the screenshot below, I want the formula to count 3 Analysts, 2 Media, 3 SMG Employees, etc.

I tried to incorporate CONTAINS, but I'm getting "Incorrect Arguement Set" (formula below)

=COUNTIF({Christoph Comms Tracker Audience Category}, CONTAINS("Select SMG Employees"))

Am I placing it wrong?

che.rabajante

Hello @nicoleejordannn

use HAS function sin it has multiple values in a cell

Hope this helps

Thanks

che.rabajante

Hello @nicoleejordannn

Please try this

COUNTIFS({Status}, "Completed", {Christoph Comms Tracker Range 1}, HAS(@cell, "All Employees"))

che.rabajante

Hello @nicoleejordannn

use HAS function sin it has multiple values in a cell

Hope this helps

Thanks

nicoleejordannn

Thank you so much, @che.rabajante! It worked!

nicoleejordannn

Hi @che.rabajante - one more element I'm adding to the formula. I only want it to count an activity if another column named "Status" has the word "Completed"

So my current formula is: =COUNTIF({Christoph Comms Tracker Range 1}, HAS(@cell, "All Employees"))

I think I need to update it to COUNTIFS and then add AND HAS(@cell, "Completed")......

Thanks in advance for any insight!\

Screenshot of sheet where I'm pulling data from:

che.rabajante

Hello @nicoleejordannn

Please try this

COUNTIFS({Status}, "Completed", {Christoph Comms Tracker Range 1}, HAS(@cell, "All Employees"))

nicoleejordannn

You are a lifesaver. Thank you so so much, it worked! @che.rabajante