Need help with a formula

D

diamond

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!
 
H

headly

Not clear, but consider a sumif function. You can say if col a data is > 0
then add up column b data
 
R

RichardSchollar

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
 
B

Bob Phillips

=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)
 
D

Dave Peterson

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 here:
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.
 
Top