Counting with Multiple Values per Cell
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!
Answers
-
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?
-
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.
-
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.
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 495 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!