Array multiple lookup

**AGriffiths73** · 09-07-2015, 01:30 PM

Hi - I'm a bit stuck, clearly or I woudn't be posting this.

I have an array that contains dynamic data, in as much as the cells reference others within the sheet. What it does is calculate the amount of effort per resource group to help me calculate the number of people I need to deliver service, and it does it by doing the following:

On the sheet, there is a list of the resource groups/roles in the company. These are selected in an array like this (I hope this comes out OK) so that per row, which represents a task, you can select up to 7 resources that are involved in carrying out the work. You then select the % of the task this resource performs. The calculation is their hourly rate as it relates to the amount of effort from them to do the work.

Resource 1 Resource 2 Resource 3 Resource 4 Resource 5 Resource 6 Resource 7

Business Admin 75% £1.64 Finance Admin 5% £0.11 Internal Sales/Admin 10% £0.18 Sales (Services) 5% £0.15 CEL 5% £0.16 Blank 0% £0.00 Blank 0% £0.00

The array I need help with is one that needs to work out how many times the resource group shows up (easy) but then add all the time they have against them in the overall array.

R1 R2 R3 R4 R5 R6 R7

Business Admin 0.012 Finance Admin 0.00 Internal Sales/Admin 0.00 Sales (Services) 0.00 CEL 0.00 Blank 0.00 Blank 0.00

The result I need is per resource, the total effort used, so Lookup/Countif resource name, then total the effort that is against that resource name.

If I haven't explained this very well, I apologise, this is just a small part of what this workbook is doing for me.

**JohnTopley** · 09-07-2015, 01:35 PM

Please post a sample file showing expected results.

Click "Go Advanced" then Paper Clip icon to upload a file.

**AGriffiths73** · 09-07-2015, 03:09 PM

Attached is the cut from the main workbook, the results aren't in it, as I don't know how to get to them, but should be:

Finance Admin 3.4 FTE (for example) which would be generated by adding together the respective values from all the cells in the array that are adjacent to the resource Business Admin.