Complex sum required based on VLOOKUP. - not working!

[littlereddog90]

I am creating a pricing template for services, a section of which is based on an hourly rate. The rates, are by level of seniority which need to be added together then multiple by the number of hours.

I would like the option of only selecting the level of seniority and adding the hours, then the total autocalculates in order to not have the column rates showing in this sheet. A simple SUM & VLOOKUP works fine at first;

=SUM(VLOOKUP(B4,Rates!A:B,2,FALSE),VLOOKUP(C4,Rates!A:B,2,FALSE),VLOOKUP(D4,Rates!A:B,2,FALSE))*E4

B4,C4 & D4 are the three fields that contain a drop down list for level of seniority. RatesA:B is a separate sheet with the levels and the associated rates so that this can be added to / modified. E4 is the number of hours.

The problem, is that there isn't always necessarily three individuals on the task so as soon as you don't fill out all three fields, the result in F4 shows up as N/A. I have tried building in IFERROR and IF/ISNA but can't get it to work. I also don't know enough about INDEX/MATCH for that to be an option. Please help!! This template needs to be clean with minimal maintenance hence I was hoping to just have one formula in F4 - is it possible?

 

[oldbrewer]

=SUM(IF(ISERROR(VLOOKUP(B4,Rates!A:B,2,FALSE),0,VLOOKUP(B4,Rates!A:B,2,FALSE)), AND SO ON........

 

[jim may]

oldbrewer - thanks...

The formula portion ISERROR( …
Where (in what position) does it end - with the closing ")"

 

[littlereddog90]

oldbrewer - thanks...

The formula portion ISERROR( …
Where (in what position) does it end - with the closing ")"

Thanks old brewer, I also have the same question though - any chance you could populate the full formula? It keeps saying I have too many arguments when I try to apply it to all three cells

 

[mohaguru]

=IF(B4="", SUM(VLOOKUP(C4,Rate!A:B,2,0),VLOOKUP(D4,Rate!A:B,2,0))*E4, IF(C4="", SUM(VLOOKUP(B4,Rate!A:B,2,0),VLOOKUP(D4,Rate!A:B,2,0))*E4, IF(D4="", SUM(VLOOKUP(B4,Rate!A:B,2,0),VLOOKUP(C4,Rate!A:B,2,0))*E4, SUM(VLOOKUP(B4, Rate!A:B,2,0),VLOOKUP(C4,Rate!A:B,2,0),VLOOKUP(D4,Rate!A:B,2,0))*E4)))

You can try this! You can make it more absulate if you add IF(AND) condition.

 

[littlereddog90]

Ah!! this is almost perfect, only problem is the if there is only one level out of the three there is nothing to "sum" so its coming back as an error. I tried building in an IF/AND so that if C4 AND D4 are blank it should multiply B4*E4 but I can't figure out where it should sit (or if that even works)

 

[oldbrewer]

=SUM(IF(ISERROR(VLOOKUP(B4,Rates!A:B,2,FALSE)),0,VLOOKUP(B4,Rates!A:B,2,FALSE)), AND SO ON........

oops I missed the ")" after FALSE ..........sorry

 

[mohaguru]

=SUM(IFERROR(VLOOKUP(B4,Rates!A:B,2,FALSE),0), IFERROR(VLOOKUP(C4,Rates!A:B,2,FALSE),0), IFERROR(VLOOKUP(D4,Rates!A:B, 2,FALSE),0))*E4

Here is another one! Hope it will work Fine!!
Simple and its returning me result in every aspect.

 

[littlereddog90]

Perfect! thanks everyone!!