Sumproduct

[sgm020]

Hi,

I have a list like below:
DATES VALUES
01.01.2004 100
05.05.2004 200
01.10.2004 300
01.03.2005 400
01.04.2005 QQQ(text)
10.07.2005 600
08.03.2006 (Blank)
01.04.2006 800
15.05.2006 900
(Blank)(Blank)
............... ........

I want to calculate between

Start date 01.01.2005
End date 31.12.2005

by the =SUMPRODUCT((A2:A20>=D22)*(A2:A20<=D23)*(B2:B20)) formula, but this formula gives #VALUE! result.

How I can solve this problem.

Thanks.

[[email protected]]

Hello,

You can enter as array formula:
=SUMPRODUCT(--(A2:A20>=D22),--(A2:A20<=D23),IF(ISERROR(--B2:B20),0,--B2:B20))
(CTRL + SHIFT + ENTER)

HTH,
Bernd

[Stefi]

=SUMPRODUCT(--(A2:A10>D22),--(A2:A10<D23),B2:B10)


Regards,
Stefi

„sgm020” ezt �*rta:

>
> Hi,
>
> I have a list like below:
> DATES VALUES
> 01.01.2004 100
> 05.05.2004 200
> 01.10.2004 300
> 01.03.2005 400
> 01.04.2005 QQQ(text)
> 10.07.2005 600
> 08.03.2006 (Blank)
> 01.04.2006 800
> 15.05.2006 900
> (Blank)(Blank)
> ............... ........
>
> I want to calculate between
>
> Start date 01.01.2005
> End date 31.12.2005
>
> by the =SUMPRODUCT((A2:A20>=D22)*(A2:A20<=D23)*(B2:B20)) formula, but
> this formula gives #VALUE! result.
>
> How I can solve this problem.
>
> Thanks.
>
>
> --
> sgm020
> ------------------------------------------------------------------------
> sgm020's Profile: http://www.excelforum.com/member.php...o&userid=26226
> View this thread: http://www.excelforum.com/showthread...hreadid=567786
>
>

[sgm020]

=SUMPRODUCT(--(A2:A10>D22),--(A2:A10<D23),B2:B10)


Regards,
Stefi

„sgm020” ezt �*rta:

>
> Hi,
>
> I have a list like below:
> DATES VALUES
> 01.01.2004 100
> 05.05.2004 200
> 01.10.2004 300
> 01.03.2005 400
> 01.04.2005 QQQ(text)
> 10.07.2005 600
> 08.03.2006 (Blank)
> 01.04.2006 800
> 15.05.2006 900
> (Blank)(Blank)
> ............... ........
>
> I want to calculate between
>
> Start date 01.01.2005
> End date 31.12.2005
>
> by the =SUMPRODUCT((A2:A20>=D22)*(A2:A20<=D23)*(B2:B20)) formula, but
> this formula gives #VALUE! result.
>
> How I can solve this problem.
>
> Thanks.
>
>
> --
> sgm020
> ------------------------------------------------------------------------
> sgm020's Profile: http://www.excelforum.com/member.php...o&userid=26226
> View this thread: http://www.excelforum.com/showthread...hreadid=567786
>
>

Thank you very much.

Best regards.