Vlookup issues

[hager]

Hello all,

I current have a spreadsheet that does a vlookup from a table that
contains the following:

VD Vacation Day 1
PD Personal Day 1
VH Half Day Vacation Day 0.5
PH Half Day Personal Day 0.5
HO Home Office 0
AS Assignment 0

I have worksheets for every month that that has the days of the month
vertically. The user will enter one of the codes in the table
according to the type of day taken.

I have a vlookup on a totals sheet:

=VLOOKUP(January2008!C5,Codes,3,FALSE)

This works fine and gives me 1 for the output when VD is entered.

Now the issue, I want to total a row based on the value in the table,
that is if the codes for three seperate dates are, PD, PH, VD the
total should be 2.5

I tried:

=VLOOKUP(January2008!C5:AB5,Codes,3,FALSE)

but it errors out, I know I can do =VLOOKUP(January2008!C5,Codes,
3,FALSE) +VLOOKUP(January2008!D5,Codes,3,FALSE)+.......

but that would take be a huge formula.

Is there a way I can sum up the vlookup without a huge formula.

Thanks to all.

Michael Hager

 

[Bernie Deitrick]

Michael,

Try

=COUNTIF(January2008!C5:AB5,"VD") +
COUNTIF(January2008!C5:AB5,"PD") +
COUNTIF(January2008!C5:AB5,"VH")/2 +
COUNTIF(January2008!C5:AB5,"PH")/2

If you want to keep it flexible, then use this:

=COUNTIF(January2008!C5:AB5,"VD")*VLOOKUP("VD",Codes,3,False)+
COUNTIF(January2008!C5:AB5,"PD")*VLOOKUP("PD",Codes,3,False) +
COUNTIF(January2008!C5:AB5,"VH")*VLOOKUP("VH",Codes,3,False) +
COUNTIF(January2008!C5:AB5,"PH")*VLOOKUP("PH",Codes,3,False)

 

[hager]

Michael,

Try

=COUNTIF(January2008!C5:AB5,"VD") +
COUNTIF(January2008!C5:AB5,"PD") +
COUNTIF(January2008!C5:AB5,"VH")/2 +
COUNTIF(January2008!C5:AB5,"PH")/2

If you want to keep it flexible, then use this:

=COUNTIF(January2008!C5:AB5,"VD")*VLOOKUP("VD",Codes,3,False)+
COUNTIF(January2008!C5:AB5,"PD")*VLOOKUP("PD",Codes,3,False) +
COUNTIF(January2008!C5:AB5,"VH")*VLOOKUP("VH",Codes,3,False) +
COUNTIF(January2008!C5:AB5,"PH")*VLOOKUP("PH",Codes,3,False)

Works like a charm...thanks much....

Michael Hager