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.

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

relective formula 0
using pivot table 3
Formula for multiple criteria on 2 worksheets 0
SUMIF Formula Help 8
Move Column Values to Rows - THANKS in Advance 3
Formula Help 3
Date Formula 1
Datedif gives wrong result 2

Top