formula help

M

mmb

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
 
M

mmb

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

Dave

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
 
M

mmb

Dave, Thank you as well.

Dave said:
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
 

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


Top