How to multiply specific values from two tables

Hello,

I am looking for the best type of formula that will allow me to multiply a value from one table to an associted value in a second table and display the result in a third table.

EXAMPLE

I have two source data tables, the first of which breaks down the number of hours members of a team have spent on a particular task:

• In each row I will have a particular activity (e.g. Activity A, Activity B, Activity C).
• In each column I will have a resource name (e.g. Resource A, Resource B, Resource C)
• The values in the table will be the number of hours each resource has spent on a particular task each month.

I want to be able to multiply the number of hours worked per month on a particular task with the hourly cost rate of that particular resource. I want to then display a summed value of these calculations in a selection of summary dashboards for wider distribution.

I'm not sure what the best formula to use to multiply the number of hours in one table, with a particular value in another table. I've attempted a combination of SUMPRODUCT and LOOKUP functions, but not sure this is best way?

I've attached a simplified version of my draft model to assist.

Try This...

Please Login or Register  to view this content.

Rem0

Originally Posted by Rem0ram

Try This...

Please Login or Register  to view this content.

Rem0

Thanks for the quick response Rem0,

Unfortunately I think that that formula may be a little too simplistic for what I require. I expect my financial to grow to be several hundred rows long as it will be used over the next 24-36 months, with dozens of resource and thousands of activities, which will not always be in the same order. I'd ideally be looking for a formula that can take one lookup value (hours per resource per activity) and then multiply it with a lookup value from the second table (hourly rate per resource for a particular date).

Thanks,

Matt

Hello,

I'm assuming that you won't have only 3 resources. And that going forward, your resources will increase too, am I correct? If that's how it is, I don't think your summary itself present the data...i.e. how many resources for activity A in each month etc.

Originally Posted by dluhut

Hello,

I'm assuming that you won't have only 3 resources. And that going forward, your resources will increase too, am I correct? If that's how it is, I don't think your summary itself present the data...i.e. how many resources for activity A in each month etc.

Yes that is correct... I'll update the example.

If that's the case, you can do it 2 ways.

1. With Excel - Try to use Power Pivot (Not the regular Pivot Table. You need to install it). If you have Excel 2013, they have "relationships"
2. With Access

Thanks - downloaded PowerPivot for Excel 2010 so will give it a shot now.

I've attached an updated dummy file. I may not be using PowerPivot correctly, but I'm struggling to generate the values I need.

I've seen a few forums using INDEX and MATCH formulas to achieve similar goals, but not sure if they will work for my example?

Thanks,

Matt

See attachment for one way to do this with a regular pivot table (with one calculated field in the pivot as well). This method would require the layout of your data to be changed.