count the number of instances in a sheet where the team name and give teams investment

We have data where team members are allotted for specific projects. Each resource has different availability, so while pulling the counts I want to multiply by the availability factor. I was thinking HLOOKUP, VLOOKUP and countif will help but not able to solve this completely. Attaching the spreadsheet. Can someone help me on this?Planning.xlsx

Hi, Welcome to the forum!

Could you tell in which cell you are multiplying the availability factor? Can you explain it with an example?

Regards,
Sindhu

ResourcePlanning sheet, column B.

All the data is in Resource planning sheet where resources (Sandhya, Lester, Swati,Avinash,Jay,Aditya,Shailesh,Yagna,Mihir etc in column B) are allocated to projects (A,B,C,D,...etc from D2 to N32) week wise (15/Oct/12 22/Oct/12 29/Oct/12 5/Nov/12 12/Nov/12 19/Nov/12 26/Nov/12 3/Dec/12 10/Dec/12 17/Dec/12 24/Dec/12 Row 1).

The resources belong to different teams (Test_Adserving, Test_Interfaces, Test_Data, Test_BRT,Dev_AdServing).

Wanted to create metrics where i can get number of resources of a specific team assigned to a project. For project "A" how many weeks are planned for team Test_Adserving?

Next step was to add the availability factor of each resource while adding the availability to the team. Hope i am clear

Teams A B C D E F
Test_Adserving 30 25 10 25 70 20 30
Test_Interfaces 0 20 0 0 35 0 0
Test_Data 0 30 0 0 15 0 0
Test_BRT 0 10 0 0 0 0 0
Dev_AdServing 30 25 10 25 70 20 30
Dev_Data 0 50 0 0 0 25 0
Dev_Interfaces 0 50 0 0 30 0 0
Dev_Interfaces_BRT 0 10 0 0 0 0 0
ReleaseLoad 60 220 20 50 220 65 60

Could you tell how 30 is arrived for Test_Adserving with team A?

There 6 As' in Test_Adserving. Three with 0.5 availability and 3 with 1 availability. So, do you want the ans to be 4.5?

In the Test_Adserving team there are 6 instance of A. I multipled it by 5 to convert weeks to days resulting to 30.

In reality it should be (2 instance of A * 0.5+3 instances of A *1)*5= number of days planned for Test_Adserving team for project A

Ok then check if this formula works for you. Enter the formula with Ctrl+Shift+Enter.

Formula:

Please Login or Register  to view this content.

Resourcing.xlsxLooks like i am doing something wrong. Attached the sheet with more details.

When i put the formula in B2 of resource allocation, it is returning 22.5 (1+1+1+0.5+0.5+0.5)*5. Is this correct?

yes it is correct

Then you can continue the same way for other cells rite? If you want to automatically take the Test_Adserving as the range,instead of manually typing, you can use indirect($A2) in place of Test_Adserving.

Please share the formula.

The same formula in post no 7

Formula:

Please Login or Register  to view this content.

Tried with the suggested formula in the attached sheet. Getting Zero or invalid values.Resourcing-Oct18.xlsx

It's an array formula. You have to enter it with Ctrl+Shift+Enter. Checked your sheet. For B2, it is giving 25 as the answer (4*0.5 + 3*1)*5.

perfect. Thanks a ton It worked.