Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 04, 2018 04:53 PM
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
Apr 05, 2018 05:47 AM
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
Apr 07, 2018 11:16 AM
Thanks - Would you have a formula handy?
Apr 07, 2018 07:47 PM
Try
IF(
{Your Variable},
VALUE(
LEFT(
{Your Variable},
FIND(',',{Your Variable})-1
)
)+
VALUE(
RIGHT(
{Your Variable},
LEN({Your Variable})-FIND(',',{Your Variable})
)
)
)
Oct 30, 2018 10:21 AM
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!
Nov 03, 2018 06:03 AM
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.
Nov 15, 2018 10:58 AM
Use:
SUM({Your Column})
Dec 23, 2018 06:57 PM
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
.)
Oct 27, 2022 07:21 AM
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!