sum issues

Options

HI Experts,

I have issues to sum my columns. The yellow column should be the sum of the column before.

I tried this formula on the sheet =column1+column2+column3.... but I get this weird result.

I also tried =Sum(column1+column2+column3..) but than the result is shows always 0.0 which is also wrong.

All columns has Text/number format.

I use this e.g. this formula in the purple column to get the numbers and similar once in the other columns

=IF(AND(Helper@row = "BU ", [ Article]@row = "NC"), "1", IF(AND([ Department]@row = "BU ", [ Article]@row = "NC"), "1,5"))

Any idea to solve it?

Thanks

Best Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Christine Menke

    I can see that the numbers in your sheet are appearing on the left side of the cell, which indicates they're being read as text values instead of numerical. This is why your formula is either returning the information all in one row or as 0, since it's unable to read the values as numerical.

    Try adding a VALUE function around each of the referenced cells, like so:

    =VALUE([Column 1]@row) + VALUE([Column 2]@row) + VALUE([Column 3]@row)


    Alternatively, if all your numbers are being output by a similar formula to the second one you listed above, you'll want to remove the quotes from the numbers so that they're input as numerical values, like so:

    =IF(AND(Helper@row = "BU ", [ Article]@row = "NC"), 1, IF(AND([ Department]@row = "BU ", [ Article]@row = "NC"), 1.5))

    See this article on Formula FAQs for more information: Why are my numeric values not calculating correctly?

    Let me know if this has helped!

    Cheers,

    Genevieve

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    Try removing the quotes from around the numbers in your IF formula. Using the quotes means it is outputting text values instead of number values, and you need the number values for adding.

Answers