Need help with a formula
 

I have a table with dates (01-01-07) in A, and then hours and minutes (01:30)
in the column B. Not every day there are entries. What i need is to know how
many hours i did in every month.
Thanks a lot for your help!

Need help with a formula
 

Not clear, but consider a sumif function. You can say if col a data is 0
then add up column b data
"diamond" wrote:

I have a table with dates (01-01-07) in A, and then hours and minutes (01:30)
in the column B. Not every day there are entries. What i need is to know how
many hours i did in every month.
Thanks a lot for your help!

Need help with a formula
 

Hi Diamond

Possibly:

=SUMPRODUCT(--(Month(A2:A100)=1),--(YEAR(A2:A100)=2007),B2:B100)

which will sum all the cells in B2:B100 which correspond to a month of
January and a year of 2007 (the criteria can be cell references
holding these values) - you can change the ranges as required.

Hope this helps!

Richard

On 28 Jan, 18:24, diamond wrote:
I have a table with dates (01-01-07) in A, and then hours and minutes (01:30)
in the column B. Not every day there are entries. What i need is to know how
many hours i did in every month.
Thanks a lot for your help!

Need help with a formula
 

=SUMPRODUCT((TEXT(A2:A100,"mmm-yy")="Jan-07")*(B2:B100))


"diamond" wrote:

I have a table with dates (01-01-07) in A, and then hours and minutes (01:30)
in the column B. Not every day there are entries. What i need is to know how
many hours i did in every month.
Thanks a lot for your help!

Need help with a formula
 

=SUMPRODUCT(--(A1:A100-DAY(A1:A100)+1 =--"2007-01-01"),B1:B100)

Note that SP cannot use whole columns, the range must be defined.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"diamond" wrote in message
...
I have a table with dates (01-01-07) in A, and then hours and minutes
(01:30)
in the column B. Not every day there are entries. What i need is to know
how
many hours i did in every month.
Thanks a lot for your help!

Need help with a formula
 

One more...
=SUMPRODUCT(--(TEXT(A2:A100,"yyyymm")="200701"),(B2:B100))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

And format the result as:
[hh]:mm

To see the results in hours and minutes.

diamond wrote:

I have a table with dates (01-01-07) in A, and then hours and minutes (01:30)
in the column B. Not every day there are entries. What i need is to know how
many hours i did in every month.
Thanks a lot for your help!

--

Dave Peterson