Sum up costs for different rates formula

[jaryszek]

Hi,

i want to sum up costs in table for different rates.

Miki Pola Cost
1 4 140 USD
2 40 USD
3 90 USD

i have a table for 2 names.

And now Miki's rate is 20 usd, Pola's rate is 30 usd per hour.
In table there is a number of hours per each Name.

So when Miki has 1 hour, Pola has 4 hours it gives us = 1 * 20 USD + 4 * 30 USD = 140 USD of costs.

How to write one easy formula to make this summing up?

Best,
Jacek

[Sam Capricci]

this would be one way... =IF(AND(A2<>"",B2<>""),A2*20+B2*30,IF(AND(A2<>"",B2=""),A2*20,IF(AND(A2="",B2<>""),B2*30,"")))

[Glenn Kennedy]

See file. This might work... but I suspect you'll need Option 2 for anything before O365...


=SUMPRODUCT(A2:B2*VLOOKUP(Table1[[#Headers],[Miki]:[Pola]],F:G,2,FALSE))

or (Option 2)

=SUMPRODUCT(A2:B2*VLOOKUP(T(INDEX(+Table1[[#Headers],[Miki]:[Pola]],)),F:G,2,FALSE))

[jaryszek]

Thank you Sam and Glenn!

What does it mean "+" next to INDEX function?

Best,
Jacek