SOLVED

Sum cells with formula

Occasional Contributor

I have built a performance tracker where an Exceed gets 1 , Meet gets 0.75 and below gets 0.40 . The issue is it will not let me sum it because the formula is read as a text 
Thie is the formula I used 

=IF(ISNUMBER(SEARCH("Exceed",E2)),"1",IF(ISNUMBER(SEARCH("Meet",E2)),"0.75",IF(ISNUMBER(SEARCH("Below",E2)),"0.40",IF(ISNUMBER(SEARCH("TBD",E2)),""))))
But when I do the sum it only reads the number added manually by Manager and not anything from q1-q4 .

Q1Q2Q3Q4ManagerY.E
   0.751.001.00
      
2 Replies
best response confirmed by Xeryar (Occasional Contributor)
Solution

Remove the double quotes from your numbers in the formula.

 

=IF(ISNUMBER(SEARCH("Exceed",E2)),1,
    IF(ISNUMBER(SEARCH("Meet",E2)),0.75,
     IF(ISNUMBER(SEARCH("Below",E2)),0.4,IF(ISNUMBER(SEARCH("TBD",E2)),""))))

@Xeryar 

"1", "0.75" and "0.40" are text values, not numbers. Change them to 1, 0.75 and 0.4, respectively.