Discussion Sum cells with formula in Excel
https://techcommunity.microsoft.com/t5/excel/sum-cells-with-formula/m-p/3032744#M125096
<P>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 <BR />Thie is the formula I used <BR /><BR />=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)),""))))<BR />But when I do the sum it only reads the number added manually by Manager and not anything from q1-q4 .<BR /><BR /></P><TABLE width="343"><TBODY><TR><TD width="54">Q1</TD><TD width="54">Q2</TD><TD width="54">Q3</TD><TD width="54">Q4</TD><TD width="63">Manager</TD><TD width="64">Y.E</TD></TR><TR><TD width="54"> </TD><TD width="54"> </TD><TD width="54"> </TD><TD width="54">0.75</TD><TD width="63">1.00</TD><TD width="64">1.00</TD></TR><TR><TD width="54"><U> </U></TD><TD width="54"><U> </U></TD><TD width="54"><U> </U></TD><TD width="54"><U> </U></TD><TD width="63"><U> </U></TD><TD width="64"><U> </U></TD></TR></TBODY></TABLE>Tue, 07 Dec 2021 14:23:31 GMTXeryar2021-12-07T14:23:31ZSum cells with formula
https://techcommunity.microsoft.com/t5/excel/sum-cells-with-formula/m-p/3032744#M125096
<P>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 <BR />Thie is the formula I used <BR /><BR />=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)),""))))<BR />But when I do the sum it only reads the number added manually by Manager and not anything from q1-q4 .<BR /><BR /></P><TABLE width="343"><TBODY><TR><TD width="54">Q1</TD><TD width="54">Q2</TD><TD width="54">Q3</TD><TD width="54">Q4</TD><TD width="63">Manager</TD><TD width="64">Y.E</TD></TR><TR><TD width="54"> </TD><TD width="54"> </TD><TD width="54"> </TD><TD width="54">0.75</TD><TD width="63">1.00</TD><TD width="64">1.00</TD></TR><TR><TD width="54"><U> </U></TD><TD width="54"><U> </U></TD><TD width="54"><U> </U></TD><TD width="54"><U> </U></TD><TD width="63"><U> </U></TD><TD width="64"><U> </U></TD></TR></TBODY></TABLE>Tue, 07 Dec 2021 14:23:31 GMThttps://techcommunity.microsoft.com/t5/excel/sum-cells-with-formula/m-p/3032744#M125096Xeryar2021-12-07T14:23:31ZRe: Sum cells with formula
https://techcommunity.microsoft.com/t5/excel/sum-cells-with-formula/m-p/3032775#M125099
<P>Remove the double quotes from your numbers in the formula.</P><P> </P><LI-CODE lang="excel-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)),""))))</LI-CODE>Tue, 07 Dec 2021 14:56:39 GMThttps://techcommunity.microsoft.com/t5/excel/sum-cells-with-formula/m-p/3032775#M125099Juliano-Petrukio2021-12-07T14:56:39ZRe: Sum cells with formula
https://techcommunity.microsoft.com/t5/excel/sum-cells-with-formula/m-p/3032777#M125100
<P><LI-USER uid="1242665"></LI-USER> </P>
<P>"1", "0.75" and "0.40" are text values, not numbers. Change them to 1, 0.75 and 0.4, respectively.</P>Tue, 07 Dec 2021 14:56:42 GMThttps://techcommunity.microsoft.com/t5/excel/sum-cells-with-formula/m-p/3032777#M125100Hans Vogelaar2021-12-07T14:56:42Z