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:D) - answer
=SUMIF(Data!C:C,LEFT(B8,3),Data!D:D) - answer
=SUMPRODUCT(--(Data!B:B=A8),--(Data!C:C=LEFT(B8,3)),(Data!D:D)) - #NUM!
Rgds

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

--
Herve Hanuise
http://www.affordsol.be


"Seeker" wrote:

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:D) - answer
=SUMIF(Data!C:C,LEFT(B8,3),Data!D:D) - answer
=SUMPRODUCT(--(Data!B:B=A8),--(Data!C:C=LEFT(B8,3)),(Data!D:D)) - #NUM!
Rgds

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

"affordsol" wrote:

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

--
Herve Hanuise
http://www.affordsol.be


"Seeker" wrote:

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:D) - answer
=SUMIF(Data!C:C,LEFT(B8,3),Data!D:D) - answer
=SUMPRODUCT(--(Data!B:B=A8),--(Data!C:C=LEFT(B8,3)),(Data!D:D)) - #NUM!
Rgds

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:D)) didn't work,
after I errase the #value, now all back to normal. Anyway thanks again for
your help.
Rgds

"affordsol" wrote:

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

--
Herve Hanuise
http://www.affordsol.be


"Seeker" wrote:

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:D) - answer
=SUMIF(Data!C:C,LEFT(B8,3),Data!D:D) - answer
=SUMPRODUCT(--(Data!B:B=A8),--(Data!C:C=LEFT(B8,3)),(Data!D:D)) - #NUM!
Rgds

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


On Oct 22, 9:02*am, Seeker wrote:
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:D) - answer
=SUMIF(Data!C:C,LEFT(B8,3),Data!D:D) - answer
=SUMPRODUCT(--(Data!B:B=A8),--(Data!C:C=LEFT(B8,3)),(Data!D:D)) - #NUM!
Rgds

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

"Seeker" wrote:

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:D)) didn't work,
after I errase the #value, now all back to normal. Anyway thanks again for
your help.
Rgds

"affordsol" wrote:

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

--
Herve Hanuise
http://www.affordsol.be


"Seeker" wrote:

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:D) - answer
=SUMIF(Data!C:C,LEFT(B8,3),Data!D:D) - answer
=SUMPRODUCT(--(Data!B:B=A8),--(Data!C:C=LEFT(B8,3)),(Data!D:D)) - #NUM!
Rgds