COUNTIF Formula for Multiple variables within a column

✭✭

Hello,

I have a sheet column that captures dropdown selections made on a Form. Users are given 4 variables to choose from. They can choose one selection or multiple selections.

I have created the following formula to capture the count of each individual selection(example):

=COUNTIF([Segment Impacted]:[Segment Impacted], "Virtual")

That formula provides me with the exact amount of times the Virtual variable was chosen.

I am looking for a formula that captures the count when ALL 4 of the available dropdown variables are selected. (i.e. Virtual1, Virtual2, Virtual3, Virtual4).

Tags:

• ✭✭✭✭✭✭

You need to use a COUNTIFS formula. Just copy what you have and just add the variables.

=COUNTIFS([Segment Impacted]:[Segment Impacted], "Virtual",[Segment Impacted]:[Segment Impacted], "Variable 2", [Segment Impacted]:[Segment Impacted], "Variable 3", etc. )

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭

Thanks! When I use the above (tailoring it to my dataset), it shows a count of zero. When I manually review the cells in this particular column I can see 4 entries where all 4 selections were made. For some reason the formular is not "seeing" the cells where all 4 are chosen.

• ✭✭✭✭✭✭

This is because of the dropdown. Something like this should work (I think):

=COUNTIF([Segment Impacted]:[Segment Impacted], AND(HAS(@cell, "Virtual 1"), HAS(@cell, "Virtual 2"), HAS(@cell, "Virtual 3"), HAS(@cell, "Virtual 4")))

Hope this helps!

• ✭✭

This formula also ends up with a count of zero. Hmmm... I wonder if there is something else that might be factoring in.

• ✭✭✭✭✭✭

Hmm, it seemed to work ok for me:

Is it because I've a space between the numbers whereas yours don't ("Virtual 1" vs "Virtual1")?

• ✭✭✭✭✭✭

So something I just realized is that both formulas are only going to count if the selection has all 4 selections.

=COUNTIF([Segment Impacted]:[Segment Impacted], OR(@cell = "Virtual 1", @cell = "Virtual 2", @cell = "Virtual 3", @cell = "Virtual 4"))

Try that

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭

This is exactly what I need the formula to do, but for some reason it is not calculating correctly for me. The count shows up as zero.

• ✭✭

Yes, I want the formula to count the cells where all 4 are selected. When I use the formula that you just provided, it give me a count of 14. Lol Not sure how it is coming up with that result. Only 3 entries in the column have all 4 selections.

• ✭✭

When I apply a filter to the sheet, it shows the correct count of 3.

• ✭✭✭✭✭✭

Its counting all cells. Try dropping all the HAS statements out of the formula and just set it up with and instead of or like my example has.

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

• ✭✭

When I try your formula it gives me a result of 14. So strange.

• ✭✭✭✭✭✭

=COUNTIF([Segment Impacted]:[Segment Impacted], "Virtual1 Virtual2 Virtual3 Virtual4")

Try that.

Jonathan Sanders, CSM

"Change is always scary because it is unknown, but facing the unknown is what makes us stronger."

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!