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

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

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

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

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

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