Planned or Forecast vs Actual Weekly to Monthly Totals

Hi Experts

I'm setting up an Planned or Forecasted vs. Actual Analysis where I want to take the weekly Planned or Forecasted Hours & Costs, then later compile those to monthly


In the attached example - There are 2 primary tables


Table 1 will be MONTHLY PLANNED and ACTUAL HOURS & COSTS per position

Tattle 2 Lists positions & 10 weeks across

Starting in Table 1 - Cell I29 - Looking to get Total Hours that "Sponsor" may work in the Month of March based on inputs given from Table 2A & 2B


See attached

Converting data from weekly to monthly is a pain in the neck since months usually start and end in the middle of a week. Here is a tool I developed a while back to convert both ways. This may help lead you to a solution for your specific situation.

6StringJazzer

Interesting file you have to convert - Although it's good, it doesn't work for my objectives

Thanks for providing feedback


All

Is there some type of Index between requested dates type of function that may work for my objectives?


Thanks

Does a Sumif(s) or INDEX between dates help with my request?

If so.... How to set up?

Thanks

No one has feedback?

Or, is there a better way to setup weekly tables & be able to compile to monthly sum totals?

I would suggest that setting up the weekly tables in row over row arrangement (aka proper data set) especially if you are not too far along with this project.
That said the following seems to yield the expected values:
1. Select cell I29 and paste the following into the formula bar:

Formula:

Please Login or Register  to view this content.

2. Hold the Ctrl and Shift keys while pressing the Enter key
3. Drag the fill handle over to cell R29
Let us know if you have any questions.

Hi JeteMc & Others

Wow!! Almost works!

I also need 2 other critical items looked up or searched for which are as follows:

Search for Position Title, such as Sponsor", "Project Manager" and others that I will have listed

Search for " P " for Planned or " A " for Actual down column G

How can these be incorporated?

Thanks

Hi JeteMc & Others

In Table 2A and Table 2B, there will be multiple positions or employees during a week to week basis. There may be 10 or 20 employees
-- These positions will also be re-listed in Table 2B but now with new calendar weeks.

I'm looking to calculate the sum total of ALL employees during the specified week beginning (Monday's) to week ending (Sunday's) PLANNED (P) Hours and later ACTUAL (A) Hours


As mentioned in previous post - 2 other critical items looked up or searched for which are as follows:

Search for Position Title, such as Sponsor", "Project Manager" and others that I will have listed

Search for " P " for Planned or " A " for Actual down column G


Thanks

The layout of the weekly data makes it hard to make the formulas in the monthly table flexible.
The array entered formula (see 2 and 3 from post #6) has been modified to get the positions from column F and the P/A notation from column G:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

JeteMc & Others

Yes - I agree the layout may not fully ideal or easy to read, which makes this type of function a bit harder to calculate.

I welcome other layouts that tracks multiple Employee Positions with Planned vs. Actual hours & costs between specified dates - Preferably, Monday - Through Sunday and Monthly

In process with reviewing your submittal

Thanks again for replying