|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 13th, 2009, 06:59 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Help -- SUMIF
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. 
|
|
#2
May 13th, 2009, 07:03 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help -- SUMIF
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.
|
|
#3
May 13th, 2009, 07:13 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help -- SUMIF
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
|
|
#4
May 13th, 2009, 07:35 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help -- SUMIF
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.
|
|
#5
May 13th, 2009, 08:47 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Help -- SUMIF
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
