Formula meeting a couple of criterias

    I need a new formula that looks at a couple of criterias before calculating.


    This is a spreadsheet I developed for work, it helps me track my time and estimated times for projects. (See Attached Spreadsheet)


    column explanations
    A7-A11 are the estimates for my projects on a weekly bases.
    B7-B11 are my Project #s.
    C7-C11 are formulas that display how much estimated project is left. D7-D11 displays any overtime I needed on a project.


    G3-G9 display the weekday totals.
    I3-I7 display the weekly project time.
    J4, J6, and J8 display miscellaneous project time totals.


    Rows 15 and below is where I store my data.


    My new feature: M2


    This area will total up the amount of time spent on a project for each day.
    This will save me time when inputing my data in the official tracking system, b/c I won't have to filter and add up times.


    My new Problem: I need a formula that looks at A15-A90 (the date), and then looks at the Project # (H15-H90) and adds the Hours (B15-B90).


    Expected:
    The expected results should be a total number of hours for each day per project.


    Thanks in advance for your help.


    Sample Attached.



    Also a manual task I must perform each week is modifying the dates in G3-G9 to reflect the current week, is there a formula that will automatically update the dates for me? and, I hate to be too picky but I back these up at the beginning of the new week (copying entire worksheet to another worksheet and delete the data and change the dates of the original), so I would need a formula or macro that I could run a to update to the current week, an persistant formula would also update my history saves.


    Again, your help and expertise is greatly appreciated.


    EDIT: I have edited the subject title for you - jiuk

    Re: I'm looking for a formula that will calculate after meeting a couple of criterias.


    Try the following. In cell N2 put the formula
    =A15
    Then in O2 put the formula =N2+1 and drag over for the rest of the days.
    Format those cells as ddd to show the abbreviatiolns of the days.
    Then in cell N4 put the formula
    =SUMPRODUCT(($A$15:$A$90=N$2)*($H$15:$H$90=$M4)*$B$15:$B$90)
    and drag to fill the table.
    That should do it.


    As to changing the dates automatically, that depends. Are the number of projects the same? The simplest way that will preserve the historical files, would be to have a cell somewhere with the starting date and then have al the other dates feed from it. That gives you just one cell to update at the start of each week.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login