Sum function returning a "0"?

Question

I am using the formula

=IF(ISNUMBER(SEARCH("PG",StreamFieldObsrv!A2)),SUM(T16,U16,V16,W16,X16,Y16),"") to return a set of numbers.

I have 5 cells that I am trying to calculate. They are number formatted. They all contain formulas as well. The formulas they contain are

=IF(ISNUMBER(SEARCH("PG",StreamFieldObsrv!A2)),IF(ISNUMBER(SEARCH("PRJ07909",$M$6)),IF(ISNUMBER(SEARCH("Group Code 9",$T$15)),"3",IF(ISNUMBER(SEARCH("Group Code 10",$T$15)),"3",IF(ISNUMBER(SEARCH("E. Coli",$T$15)),"1",""))),IF(ISNUMBER(SEARCH("PRJ07914",$M$6)),IF(ISNUMBER(SEARCH("Group Code 9",$T$15)),"3",IF(ISNUMBER(SEARCH("Group Code 10",$T$15)),"3",IF(ISNUMBER(SEARCH("E. Coli",$T$15)),"1",IF(ISNUMBER(SEARCH("BOD",$T$15)),"1","")))),IF(ISNUMBER(SEARCH("PRJ07905",$M$6)),IF(ISNUMBER(SEARCH("Group Code 9",$T$15)),"3",IF(ISNUMBER(SEARCH("Group Code

10",$T$15)),"3",IF(ISNUMBER(SEARCH("BOD",$T$15)),"1",""))),IF(ISNUMBER(SEARCH("PRJ07784",$M$6)),IF(ISNUMBER(SEARCH("Group Code 5",$T$15)),"4",IF(ISNUMBER(SEARCH("Group Code 6",$T$15)),"1",IF(ISNUMBER(SEARCH("Group Code 7",$T$15)),"3",""))),IF(ISNUMBER(SEARCH("PRJ07846",$M$6)),IF(ISNUMBER(SEARCH("Group Code 8",$T$15)),"2",""),""))))),"")

I am not sure if the formulas return of the cells are considered text therefore I don’t get a number result. That is at least what I am thinking is happening. If so How can I added the values to return a total number?

Thanks

Answer 1

Hi,

All the values this formula returns are text value because you have "3" etc in the formula and because of that a SUM function doesn't work so you have choices.

1 And IMHO best is edit your formula to remove the quotes from around the numbers.

2 Instead of =SUM(rng) to sum the numbers you can use =A1+B1+C1 doing it like this will coerce the text values into numbers.

I tried the =A1+B1+C1 and I get  #value error.

Hi,

If that gave you a #VALUE! error then I suspect there's something else in the range you're trying to sum other than these text numbers. Can you upload a copy of this workbook minus any confidential information to your Onedrive and post the link here.

Answer 2

Hi,

All the values this formula returns are text value because you have "3" etc in the formula and because of that a SUM function doesn't work so you have choices.

1 And IMHO best is edit your formula to remove the quotes from around the numbers.

2 Instead of =SUM(rng) to sum the numbers you can use =A1+B1+C1 doing it like this will coerce the text values into numbers.

Answer 3

Please provide details what you want to achieve with you big formula, so that someone from forum provide you some compact formula which would be more user friendly. 

Forget the long formula, I was trying to represent what those cells were doing. I want to sum cells together but the cells currently return a text value.

Answer 4

Hi,

All the values this formula returns are text value because you have "3" etc in the formula and because of that a SUM function doesn't work so you have choices.

1 And IMHO best is edit your formula to remove the quotes from around the numbers.

2 Instead of =SUM(rng) to sum the numbers you can use =A1+B1+C1 doing it like this will coerce the text values into numbers.

I tried the =A1+B1+C1 and I get  #value error.

Answer 5

Please provide details what you want to achieve with you big formula, so that someone from forum provide you some compact formula which would be more user friendly.