formula using dates as condition
 

trying to use a formula to specify a spend based on date criteria to give a
sum of others cells

A B C
1/01/07 $40 sum of all transactions in
january
20/01/07 $35 sum of all transactions in
February
23/01/07 $40 sum of transactions in March
2/02/07 $35
5/02/07 $40
6/03/07 $35
21/03/07 $40

I haven't been able to sort out a formula as yet and I have a lot of
worksheets to do this for so any help with this would be great

formula using dates as condition
 

try this

col A - dates
Col B - Amounts

in C1 put this formula =SUMPRODUCT((MONTH(A1:A10)=1)*(B1:B10))

the rest is for the month of Jan.




On Nov 24, 11:45*am, macadoodle
wrote:
trying to use a formula to specify a spend based on date criteria to give a
sum of others cells

A * * * * * * * * * * * * *B * * * * * * * * * * * * *C
1/01/07 * * * * * * * $40 * * * * * * * * * * *sum of all transactions in
january
20/01/07 * * * * * * *$35 * * * * * * * * * * sum of all transactions in
February
23/01/07 * * * * * * *$40 * * * * * * * * * * sum of transactions in March
2/02/07 * * * * * * * * $35
5/02/07 * * * * * * * *$40
6/03/07 * * * * * * * *$35
21/03/07 * * * * * * *$40

I haven't been able to sort out a formula as yet and I have a lot of
worksheets to do this for so any help with this would be great

formula using dates as condition
 

Try this:

=SUMPRODUCT(--(TEXT(A1:A100,"mmm-yy")="Jan-07"),B1:B100)


"macadoodle" wrote:

trying to use a formula to specify a spend based on date criteria to give a
sum of others cells

A B C
1/01/07 $40 sum of all transactions in
january
20/01/07 $35 sum of all transactions in
February
23/01/07 $40 sum of transactions in March
2/02/07 $35
5/02/07 $40
6/03/07 $35
21/03/07 $40

I haven't been able to sort out a formula as yet and I have a lot of
worksheets to do this for so any help with this would be great