Formula Help -- SUMIF

E

Evan

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.
 
R

Ron Rosenfeld

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.
Click to expand...

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
 
F

Francis

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
 
E

Evan

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
 
Top