SUMPRODUCT not working in this case...very rare

[rolo]

Hello gurus, please help me to solve this problem

In the uploaded example , there are 2 sumproduct examples (both with similar logic)
One works fine and the other not!

Please help...Thanks!

 

[NARAYANK991]

Hi ,

The problem is that the cells in rows 10 and 20 are not strictly blank , and they contain non-numeric characters. If you delete these cells by using the DEL key , your formula will work.

Narayan

 

[bosco_yip]

1] Input table data contain invisible characters of which caused the formula mistake.

2] To work around this, G3 formula :

=SUMPRODUCT((A6:A20=A3)*(D6:D20=B3)*(E5:H5=C3)*N(+E6:H20))

Regards
Bosco

 

[NARAYANK991]

Hi ,

Why not this ?

=SUMPRODUCT((A6:A20=A3)*(D6:D20=B3)*(E5:H5=C3),(E6:H20))

Narayan

 

[rolo]

@NARAYANK991 How did you detect rows containing non printable chars?

@bosco_yip Great tip!

Thank you both for your help!

 

[rolo]

Hi ,

Why not this ?

=SUMPRODUCT((A6:A20=A3)*(D6:D20=B3)*(E5:H5=C3),(E6:H20))

Narayan

I tested Bosco suggestion (adding N function). It worked fine in some cases, but fail in other cases.

NARAYANK991 suggestion worked fine in all my cases!
(although I don´t understand why using comma instead of * solved the problem)

You are great!