Summarize drop down selections in a dashboard chart

Guest Experience Shared Services

Hi all -

I created a workflow process for a guest recovery project. With each line item, I have identified drop down selections for users to summarize guest issues. Some records may have one item selected, others may have 5 items selected.

I've not found a way to create the chart using one specific column of data. The image below has two records. I am trying to summarize that data to look for trends. I currently have 800 rows.

Any thoughts are appreciated.

Thanks

Scott

Genevieve P.

Hi @Guest Experience Shared Services

You're very close! But you have additional parentheses around one of the cell references.

The HAS function works like this:

HAS(@cell, [Issue Category]@row)

You have:

HAS(@cell, ([Issue Category]@row))

Do you see the extra (these) around the cell reference?

Try:

=COUNTIF({2 Heartbeat CLOSED Range 3}, HAS(@cell, [Issue Category]@row))

You will also want to make sure the {cross sheet reference} is looking at the right column, the Multi-Select column.

Genevieve P.

Hi @Guest Experience Shared Services

What sort of trends are you looking for, and how many options do you have to select? If you're looking to see how many times each option is selected, we could create a Metric sheet to do these calculations.

You would list each selection in one cell each down a column. Then you could use a cross-sheet COUNTIF formula with HAS to see how many times that specific option was selected, like so:

=COUNTIF({Multi Column in other Sheet}, HAS(@cell, [Value Column]@row))

Does that make sense? Let me know if you'd like to see screen captures.

Cheers,

Genevieve

Guest Experience Shared Services

I have about 100+ selections we can choose from. I tried using the countif function but it is not returning the results with correct numbers. In the first example, I used countif to count with a formula referencing Issue Category

It returned one result, but when I performed a CNTRL F to search I had at least 10 uses of "Service - Front Desk. A filter on that column had not entries. I have also tried by placing the text "Service - Front Desk" in the formula. Same results.

I also have tried exporting to Excel and using a pivot table and text to columns but the export does not recognize or see the delimiter between the multiple sections.

Thoughts?

Genevieve P.

Hi @Guest Experience Shared Services

I can see that you're missing the HAS function in your COUNTIF formula.

When you specify one value in a COUNTIF, it will look to find a cell that matches that value, so in this instance it will only find the cell that has one option selected matching your criteria. This means that if your option is selected with anything else, the COUNT won't find a match because it reads all the multiple selections as one big list of text.

The HAS function enables the COUNTIF to read the individual selections and see if the cell has the one value selected along with other values. Does that make sense?

Try:

=COUNTIF({2 Heartbeat CLOSED Range 1}, HAS(@cell, [Issue Category]@row))

You should now have a COUNT of 10 instead of 1.

Cheers,

Genevieve

Guest Experience Shared Services

It makes sense but it still isn't tabulating counts. It is returning zeros.

=COUNTIF({2 Heartbeat CLOSED Range 3}, HAS(@cell, ([Issue Category]@row)))

If it makes a difference, the Issue Category column has a column type of "Dropdown Multi Select" On the data sheet 2 Heartbeat CLOSED, I can see the the message "used in formula on: Issue Category Summary

2 Heartbeat closed Issue Category (each line represents one survey)

Issue Category Summary (both columns are Text/Number types)

Genevieve P.

Hi @Guest Experience Shared Services

You're very close! But you have additional parentheses around one of the cell references.

The HAS function works like this:

HAS(@cell, [Issue Category]@row)

You have:

HAS(@cell, ([Issue Category]@row))

Do you see the extra (these) around the cell reference?

Try:

=COUNTIF({2 Heartbeat CLOSED Range 3}, HAS(@cell, [Issue Category]@row))

You will also want to make sure the {cross sheet reference} is looking at the right column, the Multi-Select column.

Guest Experience Shared Services

#Celebration! Thanks that works.

Genevieve P.

Wonderful! Thanks for letting me know 🙂