Skip to main content

Hi,



I have a Zap importing values separated by a comma - I cannot change this. So my field looks something like this: 55.00,55.00



I am attempting to create a sum from these values, but the ending value is 0.00



I do not want to create a rollup field on another sheet only to import the values back into the original sheet - that’s double redundant and another thing to keep track of.



Is there a formula that can combine those comma-separated values into a sum?



Thank you

I think you can use FIND() and INT() to extract the numbers and them sum them: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#text

I think you can use FIND() and INT() to extract the numbers and them sum them: https://support.airtable.com/hc/en-us/articles/203255215-Formula-field-reference#text


Thanks - Would you have a formula handy?

Thanks - Would you have a formula handy?


Try



IF(


    {Your Variable},


    VALUE(


        LEFT(


            {Your Variable},


            FIND(',',{Your Variable})-1


            )


        )+


    VALUE(


        RIGHT(


            {Your Variable},


            LEN({Your Variable})-FIND(',',{Your Variable})


            )


        )


    )

Try



IF(


    {Your Variable},


    VALUE(


        LEFT(


            {Your Variable},


            FIND(',',{Your Variable})-1


            )


        )+


    VALUE(


        RIGHT(


            {Your Variable},


            LEN({Your Variable})-FIND(',',{Your Variable})


            )


        )


    )



Hi there! I just tried your formula but it’s returning an error. My Variable is a lookup from another table? Would that impact it?



Many thanks for any help!



Hi there! I just tried your formula but it’s returning an error. My Variable is a lookup from another table? Would that impact it?



Many thanks for any help!


Yes — your {Variable}, when returned as a lookup, is coming across as an array. Append &'' — that is, ampersand (&) followed by an empty string ('') to each instance of {Variable} in your formula. I think that should remedy things.

Use:



SUM({Your Column})

Use:



SUM({Your Column})


Nope: Once imported as a string, her two-value variable won’t work with arithmetic functions. (Or, rather, it will work — but since such functions ignore non-numeric values, the result will always be 0.)

Try



IF(


    {Your Variable},


    VALUE(


        LEFT(


            {Your Variable},


            FIND(',',{Your Variable})-1


            )


        )+


    VALUE(


        RIGHT(


            {Your Variable},


            LEN({Your Variable})-FIND(',',{Your Variable})


            )


        )


    )

Hi,



Can this formula be amended for removing multiple commas, e.g:



1,1,1,3,1 to create a sum of 7



Thank you!

Reply


Terms & Conditions