Search code examples
arraysif-statementgoogle-sheetssumvlookup

Convert text values to int type values in google sheets from another sheet and sum it up in a sequence

Need some help on converting string text to int values.

Here is what I tried:

=IF(Survey!J2,"Myself",1,"My team",5,"Stakeholders",10,"Project",15,"")+Survey!T2,"Myself",1,"My team",5,"Stakeholders",10,"Project",15)

Need it to look for string value in sheet2 named Survey! and add this value in sheet 1 name calculator in row K2.

Expected output would be 2, if user selected myself both times. The lookup value is called Impact in sheet two row 1, is there a way I could get the code to look for "Impact" in Sheet 2 and convert the string text to numbers, depending on what the user selected and then perform a sum for all of the impact columns.

Solution

  • try:

    =IFNA(VLOOKUP(Survey!J2, {"Myself",1;"My team",5;"Stakeholders",10;"Project",15}, 2, 0))+
 IFNA(VLOOKUP(Survey!T2, {"Myself",1;"My team",5;"Stakeholders",10;"Project",15}, 2, 0))