SUM of working hours per period and per project crticicality

if we assume the 2 period ranges are always mutually exclusive you could use something like:

Formula:

Please Login or Register  to view this content.

You could do this same calculation with SUMIFS however, given layout of your source data you would need to combine 2, e.g.

Formula:

Please Login or Register  to view this content.

whichever you adopt, it would be better to store your "headers" such that each 'element' is in a unique cell (e.g. Name & Criticality) and then replace the hardwired values in the formulae with the cell reference containing the relevant parameter

XLent,

What would you mean by storing the headers?

Thank you !

so, on your result table, insert a new row such that current row 2 becomes row 3 -- in B1 enter name, e.g. John Smith, and B2 enter criticality, e.g. 1 -- then, first result (B3) is:

Formula:

Please Login or Register  to view this content.

you can then apply the above to the results matrix, and by modifying the header values in rows 1 & 2 in each column you will get the respective results.

Xlent,I just realized there is a error on my file. The table 1 should be as follow:

[Project I/D ] [Name ] [ Project Criticality ] [Period 1 End date] [Hours period 1] [Period 2 End date] [Hours period 2 ] [Period 3 End date] [Hours period 3] [Period 4 End date] [Hours period 4]

Could I still use SUMPRODUCT? What formula would you recommend?

TKs,

XLent,

You are right, please find attached the updated sample.

I have added a ''project start date'' in the file (Column G). The end date would be (column AI)

I have added some complexity, because utlimately we have 3 ressources working on a project.

- SQD (Project Database column E) is independent and have their own hours for each gates (column J, M, P, etc...)

Sourcing Buyer and Commodity Buyer are somewhat related. Ex: John Smith (Sourcing Buyer) and Anthony Smith (Commodity Buyer) cannot work on a project at the same time. John will be working on the project and his hours shall be calculated accrding to (column I, L, O, etc..) until the ''Transfer period'' is reach. This period is when he will hand over the project. Sometime there is only a sourcing buyer or a commodity buyer working on the project and therefore, no transfert period is indicated...

As for the project ''gates', they are defined starting from column H. Sometimes, there is no work associated to a ''gate'', therefore this is blank.

Hopefully, this would all make sense,

BR,

Hi, so, I think that without start date per gate this will be very complicated...

I've attached an updated version of your previous file with proposal, including results which hopefully reflect the necessary transfer date logic (see 2152/2201), but which does require the addition of the start date per gate.
{note: I've not verified the results per se, and no expected results provided to validate against}

Even then, (i.e. with this concession on format of database table), the calculations are still very intensive (and filtering, for ex., would likely generate some lag)

If this approach proves unviable, either as a result of required format change or performance, I would suggest considering a User Defined Function.

for sake of disclosure in the thread itself, I've added first calculation for each of the 3 types (per attachment):

Formula:

Please Login or Register  to view this content.

Hi Xlent,

This is exactly what I was looking for, yes, but I does lag a little bit. I wonder if it will be viable when I had more column for the entire employees... At the end I want to show these value as a graph KPI... a workload and capacity KPI.

Would a user defined function be done in power query? What would be your recommendations on the format?

Thank you,

Power Query would be an option, yes, though I would then be inclined to use Pivots directly against the PQ dataset, (latter stored in the Data Model).

Time permitting I will look at this over the weekend, should others not offer PQ solutions in the interim -- note to those that do, the attachment in tab #9 should give a dataset against which you can verify results.

Attached is a potential PQ alternative -- the Pivot is, as per prior post, run off the Data Model - i.e. the PQ script Loads to the Model, and you can connect multiple Pivots to the Model.

I've attached the rather rudimentary script below -- PQ is not my area of expertise and there are plenty of folk in the board who could no doubt optimise!

HTH

Please Login or Register  to view this content.

Xlent,

I think this works very well for me !

As a final request, is there a way I can add a ''max hours per week'' line for each individuals. I have indicated a maximum of hours per week for each ressource under column ''e'' of each sheet ''commoditiy buyer ressource list'', ''sourcing buyer ressource list'' and ''SQD ressource list''.

Many thanks!

refer attached

to facilitate, your 3 resource list tabs are converted to Tables, and imported to the model (and subsequently referenced in Model output).