Vlookup combined with SUMIF (Maybe)

Can you post a sample dataset?
I can probably assist, but it is always easier to give you the solution in excel with sample data, than in the forum.

You didnt have sample data so I made some up with simple formulas and such.
I created a pivot table on the EXACT size of the data (as in the pivot range is static). I would either grab all data or make that dynamic so I dont ever have to think about it again)

Then I added a few columns of simple logic to help parse the data in the pivot table.
I then created a calculated field that simply takes the # on time divided by total # of assignments.

If you have any questions, let me know.


Project Manager Sample (1).xlsx

I went ahead and showed you how you can use formulas instead of the pivot, but I would still rather use the pivot.


Project Manager Sample (1).xlsx

I have found being "good" at Excel is mostly being aware of what methods are available then cleverly and simply applying them together.

Also I am NOT a fan of copying and pasting stuff, OR models that are not self reliant.

To take that model one step further I would wrap a name range around the data, so it automatically updates when you add new rows. That way you can simply hit refresh for the pivot and you are done.

I created a name range with the offset function to start in cell B1, then count how many rows are NOT blank, and capture 15 columns. The named range is pivotDATA. Now whenever you add anything, you can simply hit refresh.

Updated workbook:
Project Manager Sample (1).xlsx


Reference for dynamic named ranges:
http://www.excel-easy.com/examples/d...med-range.html