• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SumProduct with changing criteria [SOLVED]

P

Phil

New Member
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
 
Hi Phil


If your codes are in Col A then maybe the following.


=SUMPRODUCT(($A$1:$A$2=100)*($C$1:$D$2))


Take care


Smallman
 
Thanks but not quite what I need.


I need the formula to be able to change based on the change in department code. If I use the formula you suggested, it will always look for "100" but I need it to recognise the different department code. Noting that the different department codes have different number of rows, so needs to be able to accommodate that as well.
 
Hi Phil


The 100 was just in there for an example. I would have thought using a bit of logic you would put a cell refereence in place of the 100. That generally makes formula flexible.


Take care


Smallman
 
Obviously I'm not logical enough!


I tried this but it didn't work. =SUMPRODUCT(($A:$A=$C276))*($I:$I)*(L:L)


Where column A searches for the dept code in cell C276 (180), Column I and Column L are the two columns I want to apply the Sumproduct to.
 
Hi Phil


Your ranges are of differing size and with Sumproduct that is not going to fly. Additionally Your example is using the whole column as a reference. Basically this is not a good idea with any formula you write. Also your formula is not like the one I gave you. There is a missing bracket at the end. Put the reference for the cells in question ie.


=SUMPRODUCT(($A2:$A100=$C276))*($I2:$I100)*($L2:$L100)


This will work. If it does not set up a dummy sheet. Make sure Col A is only Text, Col I is only Txt and Col L is ONLY numbers. You will see it goes well.


Take care


Smallman
 
Thankyou, I got it working as you suggested, I just moved the brackets a bit as per below.


=SUMPRODUCT(($A$7:$A$100=C275)*($I$7:$I$100)*($L$7:$L$100))
 
You must log in or register to reply here.
Back
Top