How to split cell data from a drop down menu and add to another sheet to count totals

NikkiOno · 09/01/22

I have a sheet1 that staff from 3 locations uses to choose items they donated. in the drop-down item column, they can choose all the items they donated (in the same row). Ex: Merlin Ono from location TNP.

Screen Shot 2022-09-01 at 1.41.07 PM.png

on a separate sheet, I need to calculate the totals for each item donated from the dropdown column by store.

so sheet 2 is totals donated

Andrée Starå · 09/03/22

@NikkiOno

Try this.

=COUNTIFS({fitting location col}, "TNP", {items donated col}, HAS(@cell, "SFG SUIT"))

Did it work now?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Andrée Starå · 09/08/22

@NikkiOno

Haha! Easy to miss!

You're more than welcome!

Glad we got it working!

SolutionSal · 09/01/22

Hello, I'm not sure if I can write an exact function for you. But I think your solution would look something like this:

=COUNTIF( {Cross Sheet Ref}, HAS(@cell, search_value))

Use COUNTIF to count occurrences of a value in a given range. Which sounds like you'll want to use a cross sheet reference as the range. Then you should be able to use a HAS function as criterion for the COUNTIF to identify if your search value appears in a multiselect.

Hope this helps!

Mike TV · 09/01/22

@SolutionSal

I said HAS won't work but I guess it will. If you wanted to search for something like "suit" it wouldn't work because you'd need to search for exactly what's in the multi-select cell such as "SFG Suit".

SolutionSal · 09/01/22

Mike TV

Sep 1, 2022

@SolutionSal
HAS won't work for a multi-select drop down column. You have to use CONTAINS. HAS requires an exact match and multiple values throws it off.

Good point. Does CONTAINS still allow for @cell to be used as the range? This would be my initial approach but maybe there isn't a function that works the way I image. Hmm

NikkiOno · 09/01/22

@SolutionSal

Thanks for your response. I tried the formula but am getting 0 and it should be 2.

=COUNTIF({items donated}, HAS({items donated cell}, "SFG Suit"))

{items donated} is the cross reference sheet/column and {items donated cell} is the cell pulling from on the cross reference sheet and the "SFG Suit" is the item I'm looking for.

TOTALS SHEET

Screen Shot 2022-09-01 at 4.36.00 PM.png

CROSS REFERENCE SHEET

Screen Shot 2022-09-01 at 4.35.33 PM.png

Andrée Starå · 09/02/22

Hi @NikkiOno

I hope you're well and safe!

Try something like this.

=COUNTIF({Items Donated}, HAS(@cell, "SFG Suit"))

And this if you want to only count by specific store.

=COUNTIFS({Store}, "TNP", {Items Donated}, HAS(@cell, "SFG Suit"))

Pro-tip: You could reference the specific Item and Store it in the sheet. Something like this.

=COUNTIFS({Store}, PARENT([Column_Name_With_The_Store_And_Item_Type]@row), {Items Donated}, HAS(@cell, [Column_Name_With_The_Store_And_Item_Type]@row))

Did that work/help?

I hope that helps!

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

NikkiOno · 09/02/22

@Andrée Starå

it still isnt working. keeps returning 0.

=COUNTIFS({fitting location col}, "TNP", {items donated col}, HAS({items donated cell}, "SFG SUIT"))

Screen Shot 2022-09-02 at 11.07.17 AM.png

Screen Shot 2022-09-02 at 11.07.38 AM.png

Andrée Starå · 09/03/22

@NikkiOno

Try this.

=COUNTIFS({fitting location col}, "TNP", {items donated col}, HAS(@cell, "SFG SUIT"))

Did it work now?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

NikkiOno · 09/05/22

@Andrée Starå

That isnt working either. What am I doing wrong?

NikkiOno · 09/07/22

@Andrée Starå

This is driving me crazy! It will not work. There must be something I am doing wrong?? I've tried CONTAINS, HAS and switched the column and row, switched the "shirt" and @row. I just don't get it.

Screen Shot 2022-09-07 at 3.34.28 PM.png

Screen Shot 2022-09-07 at 3.34.18 PM.png

Genevieve P. · 09/08/22

Hi @NikkiOno

(I hope you don't mind me jumping in here, Andrée)

@Andrée Starå's suggestions have one main difference to what you're trying. He's using a small little function called @cell. This tells the formula to check through each cell of the previously stated range:

HAS(@cell, "Value")

For example in your test formula in the most recent screen capture, try copy/pasting exactly this:

=COUNTIF([Items Donated]:[Items Donated], HAS(@cell, "Shirt"))

This says in the column "Items Donated", count how many times the word "Shirt" is selected as a value within each cell (@cell).

Then in your previous formula, the difference between yours and @Andrée Starå's is the @cell within the HAS Function.

Your Formula:

=COUNTIFS({fitting location col}, "TNP", {items donated col}, HAS({items donated cell}, "SFG SUIT"))

Should be:

=COUNTIFS({fitting location col}, "TNP", {items donated col}, HAS(@cell, "SFG SUIT"))

Does that make sense? Here's a Help Article with more information: Create Efficient Formulas with @cell and @row

Cheers,

Genevieve

NikkiOno · 09/08/22

@Genevieve P. @Andrée Starå

Oh my gosh, I m such an idiot!! I thought the @cell meant the cell row.

This totally worked! Thank you both so much.

Genevieve P. · 09/08/22

Haha no problem, it's a small thing to miss and a weird little Smartsheet function.

I'm so glad it worked!

Andrée Starå · 09/08/22

@NikkiOno

Haha! Easy to miss!

You're more than welcome!

Glad we got it working!

Formulas and Functions

Formulas and Functions

How to split cell data from a drop down menu and add to another sheet to count totals

Best Answers

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

#UNPARSEABLE when using Cross Reference

How can I display the current month number as a 2-digit number so it matches my year-month?

Symbols in Sheet Summary Pane