Counting Multiple Values in One Cell

Andrea Walters

Hello,

We have a form with a multi-select option for "Event Type". I am trying to create a new sheet that can be used within a dashboard with a chart that shows how many "Event Types" there are within a given "Delivery Format". Snippet of sheet collecting form submissions below. The formula I was using to try and find the number of event types is as follows:

=COUNTIFS({Field Events - Simple Event Range 2}, "Hybrid", {Field Events - Primary Sheet Range 1}, HAS({Field Events - Primary Sheet Range 1}, "Roadshow"))

However, it keeps coming back as "0". As you can see from the primary sheet snippet, it should come back as 1 since there is one hybrid delivery format with an event type of roadshow.

What am I doing wrong in the formula?

Thanks for your help!

Bassam Khalil

Hi @Andrea Walters

Hope you are fine, please try the following formula:

=COUNTIFS({Event Type}, HAS(@cell, "Roadshow"), {Delivery Format}, "Hybrid")

Andrea Walters

Will do - thank you, @Bassam Khalil

Bassam Khalil

Hi @Andrea Walters

Hope you are fine, please try the following formula:

=COUNTIFS({Event Type}, HAS(@cell, "Roadshow"), {Delivery Format}, "Hybrid")

Andrea Walters

Hi @Bassam Khalil - I think I am still doing something wrong as it still comes back as 0 with the following:

=COUNTIFS({Field Events - Primary Sheet Range 2}, HAS({Field Events - Primary Sheet Range 2}, "Roadshow"), {Field Events - Simple Event Range 2}, "Hybrid")

When the formula shows the "Reference Another Sheet" link, that's what I am clicking on to add in the range. Is that incorrect?

Bassam Khalil

Hi @Andrea Walters

Please try to rename the date range as i name it

{Event Type} Instead of {Field Events - Primary Sheet Range 1}

{Delivery Format} Instead of {Field Events - Simple Event Range 2}

Andrea Walters

@Bassam Khalil doing so returns #INVALID REF in the cell.

I tried both:

=COUNTIFS({Event Type}, HAS(@cell, "Roadshow"), {Delivery Format}, "Hybrid")

and

=COUNTIFS({Event Type}, HAS({Event Type}, "Roadshow"), {Delivery Format}, "Hybrid")

Bassam Khalil

@Andrea Walters 

Could you please share me as an admin on a copy of the 2 sheet (  (after removing or replacing any sensitive information) and i will create the formula for you then you can copy it to your original sheet:

My Email : [email protected]

Andrea Walters

Will do - thank you, @Bassam Khalil

Bassam Khalil

@Andrea Walters 

You are welcome and I will be happy to help you any time.

and you can understand @cell & @row in the following article