Best way to auto Sum cells with both numbers and text?

Nav

Hi everyone -

So i am trying to find a way to sum values in cells containing both number and text like "1 Macbook Pro, 2 Lenovo Tiny PC, etc. and give me the total for each item type in the totals column.

Please see the screenshot of my example smartsheet.

All of the column values are drop-down list of things we usually rent from our vendor. Usually i just manually add everything like i did in the example, but sometimes these sheets can get very long and you have to manually go through count and check everything multiple times.

So just wondering whats good way to accomplish this.

Thanks :)

Stefan

Hi@Nav,

it could probably done using complicated formulas to look for numbers in defined locations of the text strings but I suggest to separate item type columns from quantity. That way you can easily use the COUNTIF formula.

Hope this helps

Stefan

Nav

Thanks@Stefan

I was thinking about that too but that would almost double or triple the number of columns i have.

Because there are multiple different sizes for monitors, laptop types, etc.

Hopefully i can find some more info on the formulas.

Stefan

Hi@Nav,

true, requires more columns. Still I think this would be the cleaner and more stable way

Maybe you could use a hidden column to extract the number from the dropdown cells, so you can still use COUNTIF. The entries in your dropdown menu then need To follow a strict naming standard for not to confuse the number extracting formula.

hope this helps

stefan