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

https://community.smartsheet.com/discussion/comment/342140#Comment_342140

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

Cross sheet referencing, index collect for a range

how to change Parent status based on Child row checkbox?

Multiplying Cells When One Is Blank