Forum Discussion
Solved
Sum cells with formula
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 .
| Q1 | Q2 | Q3 | Q4 | Manager | Y.E |
| 0.75 | 1.00 | 1.00 | |||
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)),""))))
2 Replies
"1", "0.75" and "0.40" are text values, not numbers. Change them to 1, 0.75 and 0.4, respectively.
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)),""))))
