sum issues

Christine Menke

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

Genevieve P.

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

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.

Bassam Khalil

Hi @Christine Menke

Hope you are fine, i can't see the column name in your screenshot, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Genevieve P.

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

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.

Christine Menke

Thanks all it works!

Paul Newcome

@Genevieve P Looks like we must have been typing at the same time. Haha

@Christine Menke Happy to help. 👍️

Genevieve P.

Haha! Always good to have a second set of eyes 🙂