COUNTIFs if Column contains multiple values
Hi all,
I have a Form that users submit when an Error is detected made throughout the business.
As part of this, they select what department(s) make the error. Which inputs multiple values into the cell.
e.g. Artworks, Production etc.
I'm trying to count the number of errors per department using a CountIF formula. (I also want to know what site is submitting the error)
=COUNTIFS({Site}, Site@row, {Department Error}, "Artwork/Specifications")
The formula returns a 0, I'm guessing because it doesn't see the department because its a multiple value column. Is there any way around this?
Or maybe there is a way to use a Report, but again don't think I can group the data because its a multiple value column.
Thanks,
Jack
Best Answer
-
Hey @Jack Parry,
It should work without the helper column, I converted it into a COUNTIFS, working with this test set:
Where [Count of Artwork/Specifications] counts the occurrence of "Artwork/Specifications" in the [Multiple Items Picklist] column, based on the value in [Site]:
=COUNTIFS(Sites14:Sites18, Site@row, [Multiple Items Picklist]14:[Multiple Items Picklist]18, HAS(@cell, "Artwork/Specifications"))
I was able to return 2 occurrences for Site 1 and 1 occurrence for Site 2 using the above formula; I you test the formula without referencing {Site} and do a count of each "Artwork/Specifications" it finds in the range, does it give a count greater than 0?
Answers
-
Hey @Jack Parry,
I created something similar, using COUNTIF(), combined with HAS(), looking at a multi-item picklist column:
Where the [Count of Items] column contains this formula:
=COUNTIF([Multiple Items Picklist]:[Multiple Items Picklist], HAS(@cell, [Look For]@row))
Can you replace "Artwork/Specifications" with HAS(@cell, "Artwork/Specifications") and test to see if this works?
Hope this helps!
-
=COUNTIFS({Site}, Site@row, {Department Error}, HAS(@cell, "Artwork/Specifications")) like this?
The above still returns 0. I'm guessing I need a helper column like your Look For column?
-
Hey @Jack Parry,
It should work without the helper column, I converted it into a COUNTIFS, working with this test set:
Where [Count of Artwork/Specifications] counts the occurrence of "Artwork/Specifications" in the [Multiple Items Picklist] column, based on the value in [Site]:
=COUNTIFS(Sites14:Sites18, Site@row, [Multiple Items Picklist]14:[Multiple Items Picklist]18, HAS(@cell, "Artwork/Specifications"))
I was able to return 2 occurrences for Site 1 and 1 occurrence for Site 2 using the above formula; I you test the formula without referencing {Site} and do a count of each "Artwork/Specifications" it finds in the range, does it give a count greater than 0?
-
@EvermoreCoffee This worked, not sure why it didn't work the 1st time. Thank you! :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!