Hi
I want to be able to run a SUMPRODUCT formula for changing criteria.
I have different department codes for employees, example below.
Dept Employee Salary Proportion Effort for Product A Effort for Product B
100 Jim 10% 70% 30%
100 Bill 20% 20% 80%
200 Peter 60% 30% 70%
200 Fred 30% 40% 60%
200 Mike 70% 50% 50%
I use the formula below to capture the Sumproduct of Salary against Effort by Product.
=IFERROR(SUMPRODUCT($C$1:$C$2,D$1:D$2).
There are a lot of products, so I just copy the formula across to pick Salary Proportion against Effort for Product A, then Effort for Product B. So:
=IFERROR(SUMPRODUCT($C$1:$C$2,D$1:D$2).
=IFERROR(SUMPRODUCT($C$1:$C$2,E$1:E$2).
The problem I have is that I want the formula to automatically identify the Department code change when I drag it down. So to do the following if I copy the formula down.
Sum of Dept 100 =IFERROR(SUMPRODUCT($C$1:$C$2,D$1:D$2).
Sum of Dept 200 =IFERROR(SUMPRODUCT($C$3:$C$5,D$3:D$5).
Thanks
I want to be able to run a SUMPRODUCT formula for changing criteria.
I have different department codes for employees, example below.
Dept Employee Salary Proportion Effort for Product A Effort for Product B
100 Jim 10% 70% 30%
100 Bill 20% 20% 80%
200 Peter 60% 30% 70%
200 Fred 30% 40% 60%
200 Mike 70% 50% 50%
I use the formula below to capture the Sumproduct of Salary against Effort by Product.
=IFERROR(SUMPRODUCT($C$1:$C$2,D$1:D$2).
There are a lot of products, so I just copy the formula across to pick Salary Proportion against Effort for Product A, then Effort for Product B. So:
=IFERROR(SUMPRODUCT($C$1:$C$2,D$1:D$2).
=IFERROR(SUMPRODUCT($C$1:$C$2,E$1:E$2).
The problem I have is that I want the formula to automatically identify the Department code change when I drag it down. So to do the following if I copy the formula down.
Sum of Dept 100 =IFERROR(SUMPRODUCT($C$1:$C$2,D$1:D$2).
Sum of Dept 200 =IFERROR(SUMPRODUCT($C$3:$C$5,D$3:D$5).
Thanks