Project wise Costing

[seagull1]

Hi Friends,
I have attached an excel sheet on the monthly salary of employees and their contribution of % of work towards a project on monthly basis.

Now I need a employee list on Project wise & month wise report.

Column I (Summation of Column F, G & H) should not exceed 100% per month and that has to be validated.

Rajarajan V

[AliGW]

So what is it that you need help with?

[seagull1]

For Project id say "Project 17039", I need the List of employees and their cumulative share of Salary against that project (Project 17039) ie need total salary to be apportioned on that project against that employee.

[AliGW]

And where have you mocked this up?

[seagull1]

Whether to use Sumifs, Match INdex or whether I am to reorganize the data, I am struck.

First the salary for each month has to be apportioned for each employee and I need the sum of salary expenses against project ID

That is why I narrating the output which I am expecting.

[JeteMc]

Still not sure that I understand what you want. It would probably be easier to understand if you could manually type the output that you want generated for project 17039 so that we have something on which to base the correctness of our proposals.
That said this may be what you want:
For apportion of salary per employee:
1. Column K makes a list using*: =IFERROR(INDEX(B$2:B$12,SMALL(IF(F$2:F$12>0,ROW(B$2:B$12)-ROW(B$1)),ROWS(A$1:A1))),"")
2. Column L filters out the blanks using: =IFERROR(INDEX(K$2:K$12,MATCH(0,INDEX(COUNTIF(L$1:L1,K$2:K$12),,),)),"")
3. Column M sums the salaries using: =IF(L2="","",SUMPRODUCT((B$2:B$12=L2)*(E$2:E$12)*(F$2:F$12)))
For the apportionment of salary per month:
1. Column O makes a filtered list of months using: =IFERROR(INDEX(D$2:D$12,MATCH(0,INDEX(COUNTIF(O$1:O1,D$2:D$12),,),)),"")
2. Column P sums the salaries using: =IF(O2="","",SUMPRODUCT((D$2:D$12=O2)*(E$2:E$12)*(F$2:F$12)))
*Note that the formula in column K is array entered (Simultaneously press the Ctrl, Shift and Enter keys) and may be hidden for aesthetic purposes.
Let us know if you have any questions.

[seagull1]

Thanks JeteMc,

[JeteMc]

You're Welcome. If the issue is resolved, please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.