Log in

View Full Version : Formula involving a date, specifically a month


mike1724
Nov 18, 2009, 12:22 PM
I'm looking for assistance with what I consider a challenging formula. I want to create a
SUMIF (if that is the right function) formula that uses a month from a date to keep track of how much time I worked on a specific project. I have a "date" cell formated 1/23/2009 and an "update time" cell that I enter a number representing an amount of time (1.5 for one and a half hours). Then I have a 12 month cells (October Hours, November Hours, etc.. ) that I want to tally the update time entries with the date as the criteria. Is this possible?

Thanks in advance for any assistance,

Mike

JBeaucaire
Nov 19, 2009, 12:20 AM
The function you want is probably SUMPRODUCT().

If you click on GO ADVANCED and use the paperclip icon to upload your workbook (or smaller sample of it), I can help you construct the actual formulas to go in your summary table.

mike1724
Nov 19, 2009, 07:52 AM
I have posted an example of the spreadsheet.

Thanks J.B.

Mike

JBeaucaire
Nov 19, 2009, 02:25 PM
In G1 use this:

=SUMPRODUCT(--(MONTH($D$3:$D$13)=9), $E$3:$E$13)

Just change the "9" to "10" for October... etc.

Expand the ranges to match your data, you can allow some expansion room, but don't try to use the entire column and don't go to 50,000 rows needlessly. SUMPRODUCT() is an array formula and shouldn't be applied to unreasonably large ranges if it can be avoided.

mike1724
Nov 20, 2009, 07:50 AM
Thank you JB, I will give this a try and I bow to your expertise!

Mike