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

✭✭✭✭

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.

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

## Best Answers

• ✭✭✭✭✭✭
Answer ✓

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭✭✭
Answer ✓

Haha! Easy to miss!

You're more than welcome!

Glad we got it working!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Answers

• Employee
edited 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!

• ✭✭✭✭✭✭
edited 09/01/22

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".

• Employee

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

• ✭✭✭✭

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

CROSS REFERENCE SHEET

• ✭✭✭✭✭✭
edited 09/02/22

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

it still isnt working. keeps returning 0.

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

• ✭✭✭✭✭✭
Answer ✓

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!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

• ✭✭✭✭

That isnt working either. What am I doing wrong?

• ✭✭✭✭

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.

• Employee Admin

(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

• ✭✭✭✭

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

This totally worked! Thank you both so much.

• Employee Admin

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

I'm so glad it worked!

• ✭✭✭✭✭✭
Answer ✓

Haha! Easy to miss!

You're more than welcome!

Glad we got it working!

SMARTSHEET EXPERT CONSULTANT & PARTNER

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

W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!