Total from Dropdown column with multiple values

KatieDi · 05/17/23

Hello all.

I have a sheet with columns with multiple (20+) dropdown options. A cell can contain more than one dropdown item

I want to create a report to go up to a dashboard that shows the count of each time a dropdown is used regardless of whether it's on its own in a cell or with other items.

e.g. over 10 rows from 1 column, dropdown 1 could feature 5 times, but dropdown 2, 15 and dropdown 3 12.

I'm looking to produce a graph to demonstrate this information.

Any simple instructions that you could provide would be great - I'm not the best with formulas so it may take a bit for me to decode what you're advising! 😊

Thank you

Katie

KatieDi · 05/17/23

To add, I can get it to count up when the dropdown is the only one used in a cell, but when multiple dropdowns are used, these are not counted.

Paul Newcome · 05/17/23

You would need to use a separate sheet. Have each dropdown option listed in one column then you can use a COUTNIFS like so:

=COUNTIFS({Source Sheet Dropdown Column}, HAS(@cell, [Dropdown Column]@row))

KatieDi · 06/12/23

Hello

Thanks for your reply. However, my experience and understanding of formulas is limited so I have no idea how to go about the above formula.

I've created a new sheet. I have a column which has all the dropdown values in it that are on the original sheet.

That's as far as I can get. I don't know how to link to the source sheet or understand what to put in HAS(@cell, [Dropdown Column]@row))

Sorry. :(

Nick Korna · 06/12/23

Hi @KatieDi,

When putting the COUNTIFS formula in, you should see this:

Clicking on the "Reference Another Sheet" will open a listing of sheets (you can do a search) in which you can find the sheet containing the data and select the relevant column (in this case, for other things you might select a range - such as a VLOOKUP) which you can then give a name or leave as the default:

Click insert reference and complete the formula to get something like this:

Source data (kept simple for ease):

And the COUNTIFS:

Obviously your sheet would have a lot more options, but the theory should be the same!

Hope this helps - if anything is still unclear then just ask! 😊

Genevieve P. · 06/12/23

Hey @KatieDi

I'd be happy to jump in here and help clarify - it sounds like you're most of the way there!

In your sheet that has each individual option listed, you'll want to start building your formula by typing:

=COUNTIFS(

At this point, a pop-up helper window should appear. From here, you can select an option that says "Reference Another Sheet"

Click on this, and a pop-up window will let you choose which sheet you want to reference. Find that sheet, then find the column that has the multi-select values.

Click on the column name and it will select the entire column as a cross-sheet reference range.

You can change the name of this range in the little window at the top if you want, but you don't have to. Once you have the column highlighted, click Insert Reference. That then creates a reference to this other sheet and that specific column!

The rest you then build out in your current sheet:

=COUNTIFS({your range}, < comma

Then HAS(@cell, to say look in each cell of the previously identified range

And finally, what are you looking for? You're looking for the single value you typed in the cell to the left, the "Dropdown Column" cell.

=COUNTIFS({your range}, HAS(@cell, [Dropdown Column]@row))

The great thing about this is once you have it set up, you can right-click on the cell and turn it into a Column Formula so it automatically fills the column!

Here are some references you may find useful while building this out:

Cheers,

Genevieve

KatieDi · 06/12/23

Yeeeeeessssssssssssssssssssssssssssss!!!!

I've done it - thank you all so much. 😀

I'm the sort of person who understands that the functionality is there, I just don't understand it very well.

The only question that comes now is that I have the data source, but an empty first column in my graph....?

Any ideas on that?

Katie

Paul Newcome · 06/12/23

@KatieDi Happy to help. 👍️

Double check the widget settings. There should be a toggle in the first section where you select the data that allows you to use the first column as data labels. Does that help any?

@Nick Korna and @Genevieve P. Thanks for coming in with the assist in my absence!

Total from Dropdown column with multiple values

Answers

Categories