asked on

SUMPRODUCT not working properly

In my workbook there is a small example table of what I am trying to use.

The test data runs from B2 through to AE23

Column B and Column C have resource numbers in them.

Column D-AE have hours per day.

The SUMPRODUCT should take the value from column B, divide it by the sum of column B,C to get a percentage, then multiply it by the hours per day from column D through AE to get a daily total.

The second SUMPRODUCT should do the same, but use column C divided by the sum of B,C to get a percentage then multiply it by the hours per day from column D through AE to get a daily total.

Help.....
SUMPRODUCT.xlsx

Phillip Burton

It sounds what you want is:

=SUMPRODUCT($B2:$B23,D2:D23)/SUM($B2:$C23)

and

=SUMPRODUCT($C2:$C23,D2:D23)/SUM($B2:$C23)

This latter in column D with give 4 * 10 / 63.

Without knowing whether that is truly what you want, I cannot go any further. Is that the right answer?

DougDodge

ASKER

Sorry, not even close.

For Column D values and "MW" it should use:
B2/(SUM(B2,C2)*D2 = 0
B3/(SUM(B3,C3)*D3 = 0
B4/(SUM(B4,C4)*D4 = 0
B5/(SUM(B5,C5)*D5 = 0
B6/(SUM(B6,C6)*D6 = 0
B7/(SUM(B7,C7)*D7 = 0

all the ways down....

For Column D values and "WD" it should use:
C2/(SUM(B2,C2)*D2 = 0
C3/(SUM(B3,C3)*D3 = 0
C4/(SUM(B4,C4)*D4 = 0
C5/(SUM(B5,C5)*D5 = 0
C6/(SUM(B6,C6)*D6 = 0
C7/(SUM(B7,C7)*D7 = 40

All the way down....

Column E values and "MW" it should use:

B2/(SUM(B2,C2)*E2 = 0
B3/(SUM(B3,C3)*E3 = 0
B4/(SUM(B4,C4)*E4 = 0
B5/(SUM(B5,C5)*E5 = 0
B6/(SUM(B6,C6)*E6 = 0
B7/(SUM(B7,C7)*E7 = 0

All the ways down.....

Column E values and "WD" it should use:

C2/(SUM(B2,C2)*E2 = 20
C3/(SUM(B3,C3)*E3 = 0
C4/(SUM(B4,C4)*E4 = 0
C5/(SUM(B5,C5)*E5 = 0
C6/(SUM(B6,C6)*E6 = 0
C7/(SUM(B7,C7)*E7 = 40

All the way down.....

Phillip Burton

let's concentrate on

C7/(SUM(B7,C7)*D7 = 40

B7=0, C7=4 and D7=10

So, according to your formula, you have

4/(4+0)*10. This equals 10, not 40.

Would you please clarify what you want.

DougDodge

ASKER

Sorry, you are right Phillip.... It has been a very long day.... 10 is correct.

ASKER CERTIFIED SOLUTION

Ejgil Hedegaard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Phillip Burton

You can use:

=SUMPRODUCT($B2:$B23,D2:D23,1/($B2:$B23+$C2:$C23+0.000000000001))

and

=SUMPRODUCT($C2:$C23,D2:D23,1/($B2:$B23+$C2:$C23+0.000000000001))