Hello. I have a complex formula I need help with and struggling. Here is my
data:

A B C
1 1/1/2009 Chicago $5.00
2 1/2/2009 New York $7.00
3 2/2/2009 New York $5.00
4 2/15/2009 Chicago $9.00

Here's the struggle. Suppose I want to know shipments from January 1st to
February 1st (A) that went to New York (B) and the sum of all delivery
charges (C). It's the dates column giving me the most trouble because I am
trying to create a range (ie. all dates between 1/1/09 and 2/1/09)

Can anyone assist?? Any help would be appreciated.

Note, the table should read A1=1/1/09, B1=Chicago, and C1-$5.00, etc...

"Evan" wrote:

Hello. I have a complex formula I need help with and struggling. Here is my
data:

A B C
1 1/1/2009 Chicago $5.00
2 1/2/2009 New York $7.00
3 2/2/2009 New York $5.00
4 2/15/2009 Chicago $9.00

Here's the struggle. Suppose I want to know shipments from January 1st to
February 1st (A) that went to New York (B) and the sum of all delivery
charges (C). It's the dates column giving me the most trouble because I am
trying to create a range (ie. all dates between 1/1/09 and 2/1/09)

Can anyone assist?? Any help would be appreciated.

On Wed, 13 May 2009 10:59:15 -0700, Evan
wrote:

Hello. I have a complex formula I need help with and struggling. Here is my
data:

A B C
1 1/1/2009 Chicago $5.00
2 1/2/2009 New York $7.00
3 2/2/2009 New York $5.00
4 2/15/2009 Chicago $9.00

Here's the struggle. Suppose I want to know shipments from January 1st to
February 1st (A) that went to New York (B) and the sum of all delivery
charges (C). It's the dates column giving me the most trouble because I am
trying to create a range (ie. all dates between 1/1/09 and 2/1/09)

Can anyone assist?? Any help would be appreciated.

You might find that a Pivot Table suits your reporting requirements.

Otherwise,

=SUMPRODUCT((City="New York")*(Date=DATE(2001,1,1))*
(Date<=DATE(2009,2,1))*Amt)
--ron

Try this formula

=SUMPRODUCT(--(A1:A100=DATE(2009,1,1)),--(A1:A100<=DATE(2009,2,1)),--(B1:B100="New York"),C1:C100)
or use cell to hold thr criterias, eg.

D1 = start date
E1 = end date
F1 = City
=SUMPRODUCT(--(A1:A100=D1),--(A1:A100<=E1),--(B1:B100=F1),C1:C100)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Evan" wrote:

Note, the table should read A1=1/1/09, B1=Chicago, and C1-$5.00, etc...

"Evan" wrote:

Hello. I have a complex formula I need help with and struggling. Here is my
data:

A B C
1 1/1/2009 Chicago $5.00
2 1/2/2009 New York $7.00
3 2/2/2009 New York $5.00
4 2/15/2009 Chicago $9.00

Here's the struggle. Suppose I want to know shipments from January 1st to
February 1st (A) that went to New York (B) and the sum of all delivery
charges (C). It's the dates column giving me the most trouble because I am
trying to create a range (ie. all dates between 1/1/09 and 2/1/09)

Can anyone assist?? Any help would be appreciated.

Thanks....we're getting close. Does the format of the date in my data need
to be in any specific format for the formula to work correctly. My dates
are inputted at MM/DD/YY

"Francis" wrote:

Try this formula

=SUMPRODUCT(--(A1:A100=DATE(2009,1,1)),--(A1:A100<=DATE(2009,2,1)),--(B1:B100="New York"),C1:C100)
or use cell to hold thr criterias, eg.

D1 = start date
E1 = end date
F1 = City
=SUMPRODUCT(--(A1:A100=D1),--(A1:A100<=E1),--(B1:B100=F1),C1:C100)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Evan" wrote:

Note, the table should read A1=1/1/09, B1=Chicago, and C1-$5.00, etc...

"Evan" wrote:

Hello. I have a complex formula I need help with and struggling. Here is my
data:

A B C
1 1/1/2009 Chicago $5.00
2 1/2/2009 New York $7.00
3 2/2/2009 New York $5.00
4 2/15/2009 Chicago $9.00

Here's the struggle. Suppose I want to know shipments from January 1st to
February 1st (A) that went to New York (B) and the sum of all delivery
charges (C). It's the dates column giving me the most trouble because I am
trying to create a range (ie. all dates between 1/1/09 and 2/1/09)

Can anyone assist?? Any help would be appreciated.