SOLVED

Excel if formula vs, google sheets if formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1588174%22%20slang%3D%22en-US%22%3EExcel%20if%20formula%20vs%2C%20google%20sheets%20if%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588174%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20following%20formula%20which%20works%20great%20in%20google%20sheets%20but%20gives%20me%20a%20%22Value%22%20error%20in%20Excel%20(same%20values)%2C%20can%20you%20please%20help%20me%20with%20finding%20the%20way%20to%20get%20around%20the%20%22Value%22%20error%20in%20excel%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(E19%3D%22x%22%2C(%24D%2419*%24A%2419)%2C%22%22)%2BIF(E20%3D%22x%22%2C(%24D%2420*%24A%2420)%2C%22%22)%2BIF(E21%3D%22x%22%2C(%24D%2421*%24A%2421)%2C%22%22)%2BIF(E22%3D%22x%22%2C(%24D%2422*%24A%2422)%2C%22%22)%2BIF(E23%3D%22x%22%2C(%24D%2423*%24A%2423)%2C%22%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1588174%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588187%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20if%20formula%20vs%2C%20google%20sheets%20if%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588187%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F757877%22%20target%3D%22_blank%22%3E%40rhc002%3C%2FA%3E%26nbsp%3B%20The%20problem%20is%20that%20you%20are%20adding%20TEXT%20(%22%22)%20to%20NUMBERS.%3C%2FP%3E%3CP%3ETry%20replacing%20the%20%22%22%20in%20each%20IF%20statement%20with%200.%3C%2FP%3E%3CP%3EOr%20try%20this%20formula%20instead%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT(%24D%2419%3A%24D%2423*%24A%2419%3A%24A%2423*(%24E%2419%3A%24E%2423%3D%22x%22))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1588193%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20if%20formula%20vs%2C%20google%20sheets%20if%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1588193%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F570951%22%20target%3D%22_blank%22%3E%40mtarler%3C%2FA%3E%26nbsp%3BThank%20you%2C%20both%20solutions%20work%3C%2FP%3E%3CP%3E%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

I have the following formula which works great in google sheets but gives me a "Value" error in Excel (same values), can you please help me with finding the way to get around the "Value" error in excel?

 

IF(E19="x",($D$19*$A$19),"")+IF(E20="x",($D$20*$A$20),"")+IF(E21="x",($D$21*$A$21),"")+IF(E22="x",($D$22*$A$22),"")+IF(E23="x",($D$23*$A$23),"")

2 Replies
Highlighted
Best Response confirmed by rhc002 (New Contributor)
Solution

@rhc002  The problem is that you are adding TEXT ("") to NUMBERS.

Try replacing the "" in each IF statement with 0.

Or try this formula instead:

=SUMPRODUCT($D$19:$D$23*$A$19:$A$23*($E$19:$E$23="x"))
Highlighted

@mtarler Thank you, both solutions work