software

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)

quot;Maddoktorquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; I have the following problem.
gt;
gt; Column A are dates that I enter which is almost every date of the year.
gt; Column B has hours that I enter in relation to the date in Column A.
gt; Column C has price I enter in relation to the date in Column A.
gt;
gt; i.e.
gt;
gt; 9/1/06 5.0 25.50
gt; 10/1/06 6.0 14.00
gt; 12/1/06 5.3 12.00
gt; 2/2/06 4.0 17.50
gt;
gt; I would like to create a formula that will loom up Column A (date) and
gt; calculate the total hours and total price for all dates that fit into a
gt; particular month.
gt;
gt; i.e. For January the total hours equals 16.3 and price 51.50.
gt;
gt; Hope someone can help.
gt;
gt; 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

quot;Maddoktorquot; wrote:

gt; Hi all,
gt;
gt; I have the following problem.
gt;
gt; Column A are dates that I enter which is almost every date of the year.
gt; Column B has hours that I enter in relation to the date in Column A.
gt; Column C has price I enter in relation to the date in Column A.

gt;
gt; i.e.
gt;
gt; 9/1/06 5.0 25.50
gt; 10/1/06 6.0 14.00
gt; 12/1/06 5.3 12.00
gt; 2/2/06 4.0 17.50
gt;
gt; I would like to create a formula that will loom up Column A (date) and
gt; calculate the total hours and total price for all dates that fit into a
gt; particular month.
gt;
gt; i.e. For January the total hours equals 16.3 and price 51.50.
gt;
gt; Hope someone can help.
gt;
gt; Thanx in advance
gt;

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

B CDEFGH
201.01.200615516,5month:3
307.01.200614,542,532,7
415.01.20061366,2
512.02.20062771,1
615.02.200624,519,2
701.03.200636,532,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;quot;=quot;amp;$H$2;$D$2:$D$7)

formula for total amount in F3:

=SUMIF($C$2:$C$7;quot;=quot;amp;$H$2;$E$2:$E$7)

Hans

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