Excel SUMPRODUCT - how to deal with a #VALUE! error if array contains a text

Question

I'm using SUMPRODUCT function formula (here simplified version):

=SUMPRODUCT(--(A3:A11="aaa"),B3:B11,C3:C11*0.8)

where I want to multiply the values in column3 by a number (for example 0.8) but once there is a text value in the column I get a #VALUE! error. I've tried wrapping the array of column3 in an IFERROR and other similar functions but nothing seems to work... Any idea how I can deal with this issue?

(Note: replacing text with 0 in the table is not a solution for me as I want to keep the text there)

Answer 1

You will need to switch to an array version and add IFERROR:

=SUMPRODUCT(--(A3:A11="aaa"),B3:B11,IFERROR(C3:C11*0.8,0))

Then depending on one's version one may need to confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.