Counting with Multiple Values per Cell

meganbo

Hello!

I'm trying to count the number of times a word is used in a column. The column allows for multiple values per cell, but my formulas only count one of the words in the cell and not both of them.

For example, the values in the column are Yellow, Green, Blue, Red. There are times when Yellow and Blue need to be selected in one cell. My formula needs to show how many times all of the values are used, but currently they only count one of the values and not both in a single cell.

I've tried the following…

=COUNTIF(SheetName,"Yellow")… this will count Yellow but will not count Blue if used in the same cell.

=COUNTIFS(SheetName,"Yellow")… this will count Yellow but will not count Blue if used in the same cell.

=COUNTM(SheetName,"Yellow")… this counts all of the times "Yellow" could be used in the cell

=HAS(SheetName,"Yellow")… this counts all of the times "Yellow" could be used in the cell

Any help is appreciated to make sure my counts are accurate!

Jennifer Kurtz

Hi! The simplest way I've found to do this is to have a multi-select column with the combinations you're looking for, and use that as a reference in your formula. So, you would create a multi-select column - which I'll call Combos for the sake of an example - and choose the various combinations you want to check for/count, then you can reference that:

=countif(Range you're evaluating, HAS(@cell, Combos@row)

Your range could be in the same sheet where your data is, of course, or in another sheet — you'll just want to be sure you're referencing the appropriate range. :)

Does that help?

meganbo

Hi Jennifer,

Thank you for your message! Unfortunately, it won't work for my Dashboard calculations. I need to build graphs that need to account for the correct count of each value, and using the combo method won't work for the graphs.

I really appreciate your help though, but I'm hoping to still find a formula solution to count the multiple values in one single cell.

meganbo

Answering my own question here, as I hope this will help others who need this…

The below formula officially works if you want to count all values, even those with multiple values in one cell:

=COUNTIF({RANGE}, CONTAINS("Yellow", @cell))

I hope this helps anyone else who may want to know this.

Jennifer Kurtz

Thanks for the additional info. …I think I'm not clear on what you're trying to do (sorry!!)— but perhaps someone else here in the community will be able to help!

One last thing I'll mention, in case it's helpful: you can string together multiple COUNTIF statements in a cell, and add them together. (If the values you're counting are in a multi-select dropdown, you'd use HAS for the criteria. If they're in a text column, you'd use CONTAINS.)

that's all I got. I'll let someone else take it from here. ;) Good luck!