Search code examples
google-sheetsgoogle-sheets-formula

Summing over values in Google Sheets after applying a formula

I have a column of strings of the form

n|m

where n<m are natural numbers and I want to calculate the sum of this column where each cell has value m-n (m minus n). I can calculate the respective values into a separate cell via:

=INDEX(SPLIT(A1,"|"), 0,2)-INDEX(SPLIT(A1,"|"), 0,1)

and sum over those but I would like to do it in one step just as one formula below the specified column in one cell. Is that possible?

Thanks

Solution

  • Try in B1

    =ARRAYFORMULA(SUM(IFERROR(INDEX(SPLIT(A1:A,"|"), 0,2)-INDEX(SPLIT(A1:A,"|"), 0,1))))

    if you want to get the sum below the last value, i.e. row#8

    =ARRAYFORMULA(SUM(IFERROR(INDEX(SPLIT(A1:A8,"|"), 0,2)-INDEX(SPLIT(A1:A8,"|"), 0,1))))