Conditional Sumproduct...help!!!

**pavan210** · 10-14-2011, 06:01 AM

Please understand the attached file...it has employees...their tenure for whole year...their sal p.a....i need to calculate the total personnel cost monthly on the basis of the tenure...

i have used the method in the file...so is there any way wherein i can avoid the brown shaded portion which is used in the blue shaded cell...

appreciate the help...
thank you....

**TMS** · 10-14-2011, 06:17 AM

Try:

C49: =SUMPRODUCT(($B$3:$B$23/12)*($C$3:$C$23>=COLUMN(C$49)-2))

C53: =SUMPRODUCT($B$53*($C$3:$C$23>=COLUMN(C$49)-2)/12)

Seems to calculate the same figures but I'm not entirely sure what you're doing.

Regards

**pavan210** · 10-14-2011, 06:31 AM

thanks buddy...u solved it...i am trying to calculate the monthly cost figures on the basis of p.a. cost and tenure...

**TMS** · 10-14-2011, 06:31 AM

You're welcome. Thanks for the rep.

Please mark your thread as solved.

Regards

**Marcol** · 10-14-2011, 07:02 AM

I'm not so sure that you are calculating your data correctly on a monthly basis.

Your Range C25:N45 assumes that all personel start in month one and are there for consecutive months. This then means that if someone is present month 1 through to month 3 then again from month 6 through to month 9 the results will be returned in Months 1 to 6, is this correct?

See the attached I think it better represents your situation.
Enter or delete a value (1 or blank) in D3:O23 to see the results in C49:N49