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)
You will need to switch to an array version and add IFERROR:
Then depending on one's version one may need to confirm with Ctrl-Shift-Enter instead of Enter when exiting edit mode.