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!

SUMPRODUCT with text cells, returns #VALUE
 

Hi Gijs,

Am Fri, 7 Jul 2017 05:01:35 +0100 schrieb GijsKijlstra:

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)

try it with SUM. SUM ignores text and errors:
=SUM(IF($A$3:$H$93=$M$107;$Q$3:$Q$93*$I$3:$I$93))
and enter the formula with CTRL+Shift+Enter


Regards
Claus B.
--
Windows10
Office 2016

SUMPRODUCT with text cells, returns #VALUE
 

Hi Gijs,

Am Fri, 7 Jul 2017 08:04:51 +0200 schrieb Claus Busch:

try it with SUM. SUM ignores text and errors:
=SUM(IF($A$3:$H$93=$M$107;$Q$3:$Q$93*$I$3:$I$93))
and enter the formula with CTRL+Shift+Enter

sorry, the formula is incomplete:
=SUM(IF(($A$3:$H$93=$M$107)*ISNUMBER($Q$3:$Q$93),$ I$3:$I$93*$Q$3:$Q$93))


Regards
Claus B.
--
Windows10
Office 2016

It works like a charm! Herzlichen dank

Gijs

SUMPRODUCT with text cells, returns #VALUE
 

Hi Gijs,

Am Fri, 7 Jul 2017 08:33:37 +0100 schrieb GijsKijlstra:

It works like a charm! Herzlichen dank

you are welcome.

Without array:
=SUMPRODUCT((($A$3:$A$93=$M$107)+ISNUMBER($Q$3:$Q$ 93)),$I$3:$I$93,$Q$3:$Q$93)


Regards
Claus B.
--
Windows10
Office 2016