sumproduct problem

[Seeker]

HI,
I have sumif formulas which work fine, but when combine them as sumproduct,
result is #NUM!, why?
=SUMIF(Data!B:B,A8,Data!D) -> answer
=SUMIF(Data!C:C,LEFT(B8,3),Data!D) -> answer
=SUMPRODUCT(--(Data!B:B=A8),--(Data!C:C=LEFT(B8,3)),(Data!D)) -> #NUM!
Rgds

 

[affordsol]

Maybe You should use:
=SUMPRODUCT(SUMIF(Data!B:B,A8,Data!D),SUMIF(Data!C:C,LEFT(B8,3),Data!D))
-> #NUM!

 

[Seeker]

Hi Affordsol,
It doesn't work, would it be issue related to date because col B and A8 are
dates.
Rgds

 

[Seeker]

Dear Affordsol,
I should smash my head as I had a #Value in col D, thats why the
=SUMPRODUCT(--(Data!B:B=A8),--(Data!C:C=LEFT(B8,3)),(Data!D)) didn't work,
after I errase the #value, now all back to normal. Anyway thanks again for
your help.
Rgds

 

[muddan madhu]

=SUMPRODUCT((Data!B:B=A8)*(Data!C:C=LEFT(B8,3))*(Data!D))

 

[Barb Reinhardt]

Maybe you could add a test for ISNUMBER() to capture those errors.