Sumifs For Resource Allocation - Resource Criteria is Seperated with ";"

    Afternoon all,


    I have a query around SUMIFS and whether or not I can use it with a criteria that's delimitated with a ";".


    I have a monthly hourly forecast by project, and I'd like to allocate one or multiple resource names to each Project.


    I think MS Project does this with ease, but I have a large amount projects with other data and creating two, three or more lines per resource might be too onerous.


    Is there a way to sum the allocation of hours by resource by dividing the hours by the amount of names in the resource column?


    So where there are 80 hours in P3 for Project 1, Bob and Harry has a sum of 40 hours?



    Thanks


    Joe

    Try this formula solution for Excel 2019 and above.


    1] Create 2 define names as per attachment


    2] In "Intermate Table" H3, formula copied across down :


    =IFERROR(IF(LEFT(H$2)="P",TableRgn/IndexRgn,TableRgn),"")


    3] In "Output Table " B9, formula copies across and down :


    =SUMIFS(I:I,$H:$H,$A9)


    4] In "Output Table " B12, formula copies across :


    =SUM(B9:B11)


    Thanks for the reply - that works nicely. One question, is there a limit for the range of data this can work on? I've got over 5000 lines of data, I tried it on a small selection and it worked perfectly but failed on the larger range.


    Cheers


    Joe

    Quote from joePAIN

    Thanks for the reply - that works nicely. One question, is there a limit for the range of data this can work on? I've got over 5000 lines of data, I tried it on a small selection and it worked perfectly but failed on the larger range.


    Cheers


    Joe

    This is the limitation of formula solution, for large range of data you should switch to VBA solution or Power Query.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login