# Qlik Sense Cloud Discussions

Highlighted
New Contributor III

## qlik sense formula to add numbers in the same field

Hi,

I have a column in my table with several numbers separated by space

Is there a way to sum these numbers using a formula?

Example: 29 55 26.5 109

Tried multiple searches but no results, any help is appreciated.

Thanks

Labels (2)

• ### sum

2 Solutions

Accepted Solutions MVP

## Re: qlik sense formula to add numbers in the same field

In script you can use Subfield() to separate out the numbers and add them up. However, a quick shortcut and tricky workaround comes into mind; you can replace the space with '+' sign and evaluate it using dollar expansion like:

`=\$(=Replace('29 55 26.5 109', ' ','+'))`

New Contributor III

## Re: qlik sense formula to add numbers in the same field

So this is how i fixed it 🙂

Thank you Treseco for the guidance !!

IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',1)),0,subfield([FREIGHT INVOICE VALUE],' ',1))+
IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',2)),0,subfield([FREIGHT INVOICE VALUE],' ',2))+
IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',3)),0,subfield([FREIGHT INVOICE VALUE],' ',3))
7 Replies MVP

## Re: qlik sense formula to add numbers in the same field

In script you can use Subfield() to separate out the numbers and add them up. However, a quick shortcut and tricky workaround comes into mind; you can replace the space with '+' sign and evaluate it using dollar expansion like:

`=\$(=Replace('29 55 26.5 109', ' ','+'))`

Contributor II

## Re: qlik sense formula to add numbers in the same field

Hi,

subfield('29 55 26.5 109',' ',1)+ subfield('29 55 26.5 109',' ',2)+subfield('29 55 26.5 109',' ',3)+subfield('29 55 26.5 109',' ',4)

if it is always 4 digit number. But it is a good idea to generate a new field having all the values at row level from backend.

New Contributor III

## Re: qlik sense formula to add numbers in the same field

Hi, I tried this using the below formula but it just showed me a string of the number with + in between

Replace([FREIGHT INVOICE VALUE])

result ->

`29+55+26.5+109`

So i did num(Replace([FREIGHT INVOICE VALUE], ' ','+'),'#,##0.##') instead, but got null as a result

Note: It would not accept the \$ sign in the edited column/field

`\$(`

New Contributor III

## Re: qlik sense formula to add numbers in the same field

I meant this for the first attempt
Replace([FREIGHT INVOICE VALUE], ' ','+')
New Contributor III

## Re: qlik sense formula to add numbers in the same field

Hi Miskinmaz,

so basically I have a column containing different numbers and it varies.

the numbers are separated by space to indicate different amounts which used to be summed in excel

moving to QLIK, I can't mimic the excel formulas and a bit lost, don't think your method would work for me. My approach was to use the built in "split" mechanism in the table but it does not exist for this specific column i think due to data profiling by qlik

Contributor II

## Re: qlik sense formula to add numbers in the same field

Yes I agree if number varies then my solution won't work. But Tresesco solution should work for you. I guess u missed the \$ and = sign
New Contributor III

## Re: qlik sense formula to add numbers in the same field

So this is how i fixed it 🙂

Thank you Treseco for the guidance !!

IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',1)),0,subfield([FREIGHT INVOICE VALUE],' ',1))+
IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',2)),0,subfield([FREIGHT INVOICE VALUE],' ',2))+
IF(ISNULL(subfield([FREIGHT INVOICE VALUE],' ',3)),0,subfield([FREIGHT INVOICE VALUE],' ',3))