Forum Discussion

rhc002's avatar
rhc002
Copper Contributor
Aug 14, 2020
Solved

Excel if formula vs, google sheets if formula

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),"")

excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Aug 14, 2020

    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"))

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Aug 14, 2020

    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"))

Resources