COUNTIFs if Column contains multiple values

Jack Parry

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

EvermoreCoffee

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

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!

Jack Parry

@EvermoreCoffee

=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?

EvermoreCoffee

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?

Jack Parry

@EvermoreCoffee This worked, not sure why it didn't work the 1st time. Thank you! :)