Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Formula involving a date, specifically a month (https://www.askmehelpdesk.com/showthread.php?t=417200)

  • Nov 18, 2009, 12:22 PM
    mike1724
    Formula involving a date, specifically a month
    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
  • Nov 19, 2009, 12:20 AM
    JBeaucaire

    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.
  • Nov 19, 2009, 07:52 AM
    mike1724
    1 Attachment(s)
    I have posted an example of the spreadsheet.

    Thanks J.B.

    Mike
  • Nov 19, 2009, 02:25 PM
    JBeaucaire

    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.
  • Nov 20, 2009, 07:50 AM
    mike1724

    Thank you JB, I will give this a try and I bow to your expertise!

    Mike

  • All times are GMT -7. The time now is 03:58 AM.