I have a worksheet that is laid out in two sections

The first is as follows:
A B C D F G H I
1 Task PM EE ME Total Jan Feb Mar
2 Controls 2 5 7 14 5 6 3
3 M&S 3 7 10 20 7 7 6
Total 5 12 17
Note: Total is spread manually over months
The second section is below the first & looks like

F G H I J
6 Labor Jan Feb Mar Total
7 PM 1.76 1.9 1.33 5
8 EE 4.24 4.59 3.17 12
9 ME 6 6.5 4.5 17

The end objective is to summarize the labor by month. So in cell G6 i would
like a way to run the formula (B2/F2)*G2 but i need that formula to run for
every row that has PM hours so i end up with total PM hours for Jan etc for
all labor categories. In the end the totals in the column J of the second
section should equal the total row 5 of the first section.

I've tried a sumproduct with no luck. I'm really good with formulas & i'm
pretty good with macros so any type of solution will work.

Any suggestions at all are greatly appreciated.

Also Note that an If Then isn't optimal because there will typically be
several rows of task involved.

Thanks

What happened to column E??
For this solution, I'm going to assume that there is a column E between D
and F and thatyou just didn't show it. If column references were off, then
you'll need to adjust these formulas.

In G7, put this formula:
=(SUMPRODUCT(($B$2:$B$3),--(G$2:G$30))/SUMPRODUCT(($F$2:$F$3),--(G$2:G$30)))*SUM(G$2:G$3)

In G8, put this formula:
=(SUMPRODUCT(($C$2:$C$3),--(G$2:G$30))/SUMPRODUCT(($F$2:$F$3),--(G$2:G$30)))*SUM(G$2:G$3)

In G9, put this formula:
=(SUMPRODUCT(($D$2:$D$3),--(G$2:G$30))/SUMPRODUCT(($F$2:$F$3),--(G$2:G$30)))*SUM(G$2:G$3)

Note that the column identifier for the first range (B2:B3,C2:C3, and D2:D3)
is the only thing that changes in each one. You'll have to make that change
manually, be sure to use $ symbols as shown in the formulas above.

Now you can take those formulas and fill them to the right to go under each
of the months (Feb, Mar) in that section and they'll give you the correct
results. The references in each to column G will automatically change to H
and I (and to other columns as you insert months between the current Mar
&Total columns and continue to fill the formula out for the new months).

The other thing you might have to change manually would be the row numbers
involved if they are other than 2 and 3.




"Chas" wrote:

I have a worksheet that is laid out in two sections

The first is as follows:
A B C D F G H I
1 Task PM EE ME Total Jan Feb Mar
2 Controls 2 5 7 14 5 6 3
3 M&S 3 7 10 20 7 7 6
Total 5 12 17
Note: Total is spread manually over months
The second section is below the first & looks like

F G H I J
6 Labor Jan Feb Mar Total
7 PM 1.76 1.9 1.33 5
8 EE 4.24 4.59 3.17 12
9 ME 6 6.5 4.5 17

The end objective is to summarize the labor by month. So in cell G6 i would
like a way to run the formula (B2/F2)*G2 but i need that formula to run for
every row that has PM hours so i end up with total PM hours for Jan etc for
all labor categories. In the end the totals in the column J of the second
section should equal the total row 5 of the first section.

I've tried a sumproduct with no luck. I'm really good with formulas & i'm
pretty good with macros so any type of solution will work.

Any suggestions at all are greatly appreciated.