Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
Hello Guys, I need help on this one. I need a formula like this-- grand
total from each cost # by month. ig: cost 366 total for the month of March 08 is $6600 and cost #2731 for March 08 is $2130. Cost # Date Submitted Quoted Amount 366 3/24/2008 3000 366 3/26/2008 3600 2100 3/4/2008 1000 2493 3/24/2008 450 2617 3/28/2008 4475 2731 3/27/2008 1050 2731 3/12/2008 1080 2980 3/26/2008 1000 1139 4/1/2008 1000 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
try this
=SUMPRODUCT((A2:A22=366)*(MONTH(B2:B22)=3)*C2:C22) -- Don Guillett Microsoft MVP Excel SalesAid Software "mmb" wrote in message ... Hello Guys, I need help on this one. I need a formula like this-- grand total from each cost # by month. ig: cost 366 total for the month of March 08 is $6600 and cost #2731 for March 08 is $2130. Cost # Date Submitted Quoted Amount 366 3/24/2008 3000 366 3/26/2008 3600 2100 3/4/2008 1000 2493 3/24/2008 450 2617 3/28/2008 4475 2731 3/27/2008 1050 2731 3/12/2008 1080 2980 3/26/2008 1000 1139 4/1/2008 1000 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
Don, you are awesome. Thank you so much for your help.
I just want to understand the formula. The #3 in here is the month right? MONTH(B2:B22)=3 Thanks again. Have a nice weekedn. "Don Guillett" wrote: try this =SUMPRODUCT((A2:A22=366)*(MONTH(B2:B22)=3)*C2:C22) -- Don Guillett Microsoft MVP Excel SalesAid Software "mmb" wrote in message ... Hello Guys, I need help on this one. I need a formula like this-- grand total from each cost # by month. ig: cost 366 total for the month of March 08 is $6600 and cost #2731 for March 08 is $2130. Cost # Date Submitted Quoted Amount 366 3/24/2008 3000 366 3/26/2008 3600 2100 3/4/2008 1000 2493 3/24/2008 450 2617 3/28/2008 4475 2731 3/27/2008 1050 2731 3/12/2008 1080 2980 3/26/2008 1000 1139 4/1/2008 1000 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
Hi,
I think he wants month and year: =SUMPRODUCT((A2:A22=366)*(AND(MONTH(B2:B22)=3,YEAR (B2:B22)=2008))*C2:C22) If you want to use cell references for the 2 criteria, eg Cost# in E1, Date in F1: =SUMPRODUCT(--(A1:A9=E1)*(AND(MONTH(B1:B9)=MONTH(F1),YEAR(B1:B9) =YEAR(F1)))*(C1:C9)) F1 requires full date. eg, for March 2008, enter 3/1/2008 Regards - Dave "Don Guillett" wrote: try this =SUMPRODUCT((A2:A22=366)*(MONTH(B2:B22)=3)*C2:C22) -- Don Guillett Microsoft MVP Excel SalesAid Software |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
Didn't say so -- Don Guillett Microsoft MVP Excel SalesAid Software "Dave" wrote in message ... Hi, I think he wants month and year: =SUMPRODUCT((A2:A22=366)*(AND(MONTH(B2:B22)=3,YEAR (B2:B22)=2008))*C2:C22) If you want to use cell references for the 2 criteria, eg Cost# in E1, Date in F1: =SUMPRODUCT(--(A1:A9=E1)*(AND(MONTH(B1:B9)=MONTH(F1),YEAR(B1:B9) =YEAR(F1)))*(C1:C9)) F1 requires full date. eg, for March 2008, enter 3/1/2008 Regards - Dave "Don Guillett" wrote: try this =SUMPRODUCT((A2:A22=366)*(MONTH(B2:B22)=3)*C2:C22) -- Don Guillett Microsoft MVP Excel SalesAid Software |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula help
Dave, Thank you as well.
"Dave" wrote: Hi, I think he wants month and year: =SUMPRODUCT((A2:A22=366)*(AND(MONTH(B2:B22)=3,YEAR (B2:B22)=2008))*C2:C22) If you want to use cell references for the 2 criteria, eg Cost# in E1, Date in F1: =SUMPRODUCT(--(A1:A9=E1)*(AND(MONTH(B1:B9)=MONTH(F1),YEAR(B1:B9) =YEAR(F1)))*(C1:C9)) F1 requires full date. eg, for March 2008, enter 3/1/2008 Regards - Dave "Don Guillett" wrote: try this =SUMPRODUCT((A2:A22=366)*(MONTH(B2:B22)=3)*C2:C22) -- Don Guillett Microsoft MVP Excel SalesAid Software |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|