I have two sheets like below, what I would like is an excel sumproduct-like formula which multiplies the ( time with the registry (the one under the name) and the factor on the second sheet corresponding with the frequency (=1/days) ) *7
for example: for Albert: 60*0*0,047619+60*0*0.02381+60*0*0.032258+30*1*0.1428+60*1*0.01087+60*1*0.142857+60*0*0.016393 *7 = 94.56522 ~ 95 min
using the sumproduct of course we can multiply the various columns on the firstsheet. I could add a column for the factor but this creates another problem in conformity and compatibility
I would need in the total cells is something like this, but of course the vlookup wouldn't work.
SUMPRODUCT($B2:$B8 , $C1:$C8 , VLOOKUP($A1:$A8 , frequencysheet!A$1:C$10 , 3 , 0)) *7
Hope it is clear. I translated from Dutch so pardon the punctuation which is different in the Dutch version
basesheet
frequencysheet
for example: for Albert: 60*0*0,047619+60*0*0.02381+60*0*0.032258+30*1*0.1428+60*1*0.01087+60*1*0.142857+60*0*0.016393 *7 = 94.56522 ~ 95 min
using the sumproduct of course we can multiply the various columns on the firstsheet. I could add a column for the factor but this creates another problem in conformity and compatibility
I would need in the total cells is something like this, but of course the vlookup wouldn't work.
SUMPRODUCT($B2:$B8 , $C1:$C8 , VLOOKUP($A1:$A8 , frequencysheet!A$1:C$10 , 3 , 0)) *7
Hope it is clear. I translated from Dutch so pardon the punctuation which is different in the Dutch version
basesheet
frequencysheet