Creating a chart with a multiple dropdown field

Bruce Southers

Hello,

I have tried a few options, but no success. Trying to get a report in a pie or bar chart format that will be reporting off of a field in a sheet with multiple options. The sheet is a list of company projects and the field is a strategic category (Go to Market, Client Service, People & Culture). Some projects are only one category, but other projects have multiple selected. The screenshot attached shows some of the fields, and in the example, People & Culture should be counted 2x, Client Service 2x, Go to Market 1x, and Financial Health 1x.

The C-suite needs to see how many projects are in each category. Any assistance with the formula is greatly appreciated!

Bruce

Genevieve P.

Hi @Bruce Southers

No need to create anything new, this is perfect! We can just adjust your current formula. Right now you're looking for the exact match of the cell to the left. This means it will only count the value if it's selected on its own without anything else. We can add the HAS function into your current formula:

=COUNTIF({Strategic Category}, HAS(@cell, Label2))

I would also suggest taking away the row number (2) and replacing it with @row. @row just tells the formula to look at the cell in this current row so you can copy/paste it into other cells and it will automatically look to the left instead of up or down in the sheet.

=COUNTIF({Strategic Category}, HAS(@cell, Label@row))

Let me know if this works for you!

Cheers,

Genevieve

Genevieve P.

Hi @Bruce Southers

What I would do is set up a second "Metric" sheet that has all of your multi-select options listed down one column as single values.

Then you can use a COUNTIF statement to count how many times each individual selection appears in the Strategic Category column of your main sheet. You'll need to use HAS to see if the cell has that value, with or without others.

For example:

=COUNTIF({Strategic Category}, HAS(@cell, [Strategic Category]@row))

Let me know if that makes sense or if it would be helpful to see screen captures.

Cheers,

Genevieve

Bruce Southers

Thanks! Here is the metric sheet I have set up, but full disclosure, do not have a lot of experience with metrics. I have generally just reused existing SmartSheet metric sheets. Here is the formula: =COUNTIF({Strategic Category}, Label2).

Questions: do I need two separate metrics sheets? And if so, the other sheet would use the formula you provided?

=COUNTIF({Strategic Category}, HAS(@cell, [Strategic Category]@row))

Genevieve P.

Hi @Bruce Southers

No need to create anything new, this is perfect! We can just adjust your current formula. Right now you're looking for the exact match of the cell to the left. This means it will only count the value if it's selected on its own without anything else. We can add the HAS function into your current formula:

=COUNTIF({Strategic Category}, HAS(@cell, Label2))

I would also suggest taking away the row number (2) and replacing it with @row. @row just tells the formula to look at the cell in this current row so you can copy/paste it into other cells and it will automatically look to the left instead of up or down in the sheet.

=COUNTIF({Strategic Category}, HAS(@cell, Label@row))

Let me know if this works for you!

Cheers,

Genevieve

Bruce Southers

That worked, thank you very much!!

Genevieve P.

No problem! I'm glad I could help 🙂