Adding a column that has IF fomulas

[Nicole .]

Hi,

In Excel I have a column of "if" statements where the results are numerical values.

When I go to another column and insert =sum(C4:C78) I get a zero answer.

How can I add up the results in the new cell?

[#2 · Accepted solution]

Hi, 

Your problem is:

These numbers are considered as texts, so SUM ignored them, and returns 0 since there are no numbers to add!

You can check that by using ISTEXT function, just apply it to one of these numbers, and you will get TRUE.

The solution is:

Convert these text numbers to actual numbers by using one of the following methods:

#1

Go to the last of the IF function after the right parenthesis and type: +0

=IF(logical_test,value_if_true,value_if_false)+0

 Then apply this update to all existing cells.

#2

Replace this formula:

=SUM(C4:C78)

With this:

=SUMPRODUCT(VALUE(C4:C78))

Hope that helps. 

[#3]

Excellent - that has fixed the problem. And was so easy :) Thank you. 

I din't need to change the second formula as it just added the numbers!

[#4]

I have tried to add a column that is not responding. I copied the numbers to the next column one at a time and then =SUM and it worked. I understand that the first column is being considered text as there are equation there adding the horizontal information. However, after trying the previous response it is still just adding to zero. I've tried turning it off and turning it back on, asking a co-worker and your help here! Maybe I'll just go home for the day and try again tomorrow.