sum if and between dates

[mathewheys]

Hi all

I am trying to sum information based on the following conditions

Cell E3 = Sum if = Account A and between >= 20/6/05 and <=24/6/05
The Answer would be 279

A B C D E
1 19/06/2005 Account A 125 20/06/2005
2 20/06/2005 Account A 150 24/06/2005
3 21/06/2005 Account B 156 Account A
4 22/06/2005 Account B 135
5 23/06/2005 Account C 142
6 24/06/2005 Account A 129
7 25/06/2005 Account C 200
8 26/06/2005 Account A 300

[KL]

Hi,

Try this:

=SUMPRODUCT((A1:A8>=DATE(2005,6,20))*(A1:A8<=DATE(2005,6,24))*(UPPER(B1:B8)="ACCOUNT
A")*C1:C8)

or

=SUMPRODUCT((A1:A8>=DATE(2005,6,20))*(A1:A8<=DATE(2005,6,24))*ISNUMBER(SEARCH("Account
A",B1:B8))*C1:C8)

Regards,
KL


"mathewheys" <[email protected]> wrote
in message news:[email protected]...
>
> Hi all
>
> I am trying to sum information based on the following conditions
>
> Cell E3 = Sum if = *Account A* and between *>= 20/6/05 and <=24/6/05*
> The Answer would be 279
>
> A B C D
> E
> 1 19/06/2005 Account A 125 20/06/2005
> 2 20/06/2005 Account A 150 24/06/2005
> 3 21/06/2005 Account B 156 Account A
> 4 22/06/2005 Account B 135
> 5 23/06/2005 Account C 142
> 6 24/06/2005 Account A 129
> 7 25/06/2005 Account C 200
> 8 26/06/2005 Account A 300
>
>
> --
> mathewheys
> ------------------------------------------------------------------------
> mathewheys's Profile:
> http://www.excelforum.com/member.php...o&userid=25093
> View this thread: http://www.excelforum.com/showthread...hreadid=386307
>

[RagDyer]

It's perhaps best to enter your parameters into separate cells so that
changes to these variables are more easily accomplished.
I assume that's the reason for your entries in Column D.

Start date in D1
End date in D2
Acct. name in D3

And try this:

=SUMPRODUCT((A1:A50>=D1)*(A1:A50<=D2)*(B1:B50=D3)*C1:C50)
--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"mathewheys" <[email protected]> wrote
in message news:[email protected]...
>
> Hi all
>
> I am trying to sum information based on the following conditions
>
> Cell E3 = Sum if = *Account A* and between *>= 20/6/05 and <=24/6/05*
> The Answer would be 279
>
> A B C D
> E
> 1 19/06/2005 Account A 125 20/06/2005
> 2 20/06/2005 Account A 150 24/06/2005
> 3 21/06/2005 Account B 156 Account A
> 4 22/06/2005 Account B 135
> 5 23/06/2005 Account C 142
> 6 24/06/2005 Account A 129
> 7 25/06/2005 Account C 200
> 8 26/06/2005 Account A 300
>
>
> --
> mathewheys
> ------------------------------------------------------------------------
> mathewheys's Profile:
http://www.excelforum.com/member.php...o&userid=25093
> View this thread: http://www.excelforum.com/showthread...hreadid=386307
>