SUMPRODUCT with text cells, returns #VALUE
INTRODUCTION: based criteria in columns A through H, matching column M, numbers in column Q need to be multiplied with numbers in column i, and the result added
=SUMPRODUCT(($A$3:$H$93=$M107)*(Q$3:Q$93)*($I$3:$I $93))
ISSUE: column Q contains besides numbers, also text. (without text in column Q, the formula works like a charm. However with the text in columns Q, it returns #VALUE)
I HAVE TRIED =SUMPRODUCT(($A$3:$H$93=$M107)*ISNUMBER(Q$3:Q$93)* ($I$3:$I$93)) but to no avail: it returns a number that looks like counting the number of cells that match the criteria iso adding them.
Can anyone help me, please!
|