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

The values were returning a text format but I went and chaged the format to number and it worked.