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

Nav
Nav ✭✭
edited 08/05/20 in Formulas and Functions

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 :)


Tags:

Answers

  • Stefan
    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

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

  • Nav
    Nav ✭✭
    edited 08/07/20

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

    Smartsheet Consulting, Solution Building, Training and Support.

    Projects for Processes and for People.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!