Hi all,

I have the following problem.

Column A are dates that I enter which is almost every date of the year.
Column B has hours that I enter in relation to the date in Column A.
Column C has price I enter in relation to the date in Column A.

i.e.

9/1/06 5.0 25.50
10/1/06 6.0 14.00
12/1/06 5.3 12.00
2/2/06 4.0 17.50

I would like to create a formula that will loom up Column A (date) and
calculate the total hours and total price for all dates that fit into a
particular month.

i.e. For January the total hours equals 16.3 and price 51.50.

Hope someone can help.

Thanx in advance

=sumproduct(--(MONTH(A1:A1000=1),B1:B100)

for the hours, c1:C1000 for the price.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Maddoktor" wrote in message
...
Hi all,

I have the following problem.

Column A are dates that I enter which is almost every date of the year.
Column B has hours that I enter in relation to the date in Column A.
Column C has price I enter in relation to the date in Column A.

i.e.

9/1/06 5.0 25.50
10/1/06 6.0 14.00
12/1/06 5.3 12.00
2/2/06 4.0 17.50

I would like to create a formula that will loom up Column A (date) and
calculate the total hours and total price for all dates that fit into a
particular month.

i.e. For January the total hours equals 16.3 and price 51.50.

Hope someone can help.

Thanx in advance

Try:

=SUMPRODUCT(--(MONTH(A1:A100)=1),B1:B100)
=SUMPRODUCT(--(MONTH(A1:A100)=1),C1:C100)

Note...you can not use whole column reference in a sumproduct formula!

HTH
Jean-Guy

"Maddoktor" wrote:

Hi all,

I have the following problem.

Column A are dates that I enter which is almost every date of the year.
Column B has hours that I enter in relation to the date in Column A.
Column C has price I enter in relation to the date in Column A.


i.e.

9/1/06 5.0 25.50
10/1/06 6.0 14.00
12/1/06 5.3 12.00
2/2/06 4.0 17.50

I would like to create a formula that will loom up Column A (date) and
calculate the total hours and total price for all dates that fit into a
particular month.

i.e. For January the total hours equals 16.3 and price 51.50.

Hope someone can help.

Thanx in advance

Provided I got your problem, here's a proposal:

B C D E F G H
2 01.01.2006 1 5 51 6,5 month: 3
3 07.01.2006 1 4,5 42,5 32,7
4 15.01.2006 1 3 66,2
5 12.02.2006 2 7 71,1
6 15.02.2006 2 4,5 19,2
7 01.03.2006 3 6,5 32,7

Add a help column (C) that holds the month of the date in B

=MONTH(B2)

Copy down as required

Put your month in H2 and your

formula for total hours in F2:

=SUMIF($C$2:$C$7;"="&$H$2;$D$2:$D$7)

formula for total amount in F3:

=SUMIF($C$2:$C$7;"="&$H$2;$E$2:$E$7)

Hans

Ps: Mind the $-signs which make the references FIX!