Aug 13 2020 05:48 PM
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),"")
Aug 13 2020 06:02 PM
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"))
Aug 13 2020 06:14 PM
@mtarler Thank you, both solutions work
:)
Aug 13 2020 06:02 PM
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"))