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

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",Cod es,3,False)+
COUNTIF(January2008!C5:AB5,"PD")*VLOOKUP("PD",Code s,3,False) +
COUNTIF(January2008!C5:AB5,"VH")*VLOOKUP("VH",Code s,3,False) +
COUNTIF(January2008!C5:AB5,"PH")*VLOOKUP("PH",Code s,3,False)

--
HTH,
Bernie
MS Excel MVP


wrote in message
...
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

On Jan 15, 12:19 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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",Cod es,3,False)+
COUNTIF(January2008!C5:AB5,"PD")*VLOOKUP("PD",Code s,3,False) +
COUNTIF(January2008!C5:AB5,"VH")*VLOOKUP("VH",Code s,3,False) +
COUNTIF(January2008!C5:AB5,"PH")*VLOOKUP("PH",Code s,3,False)

--
HTH,
Bernie
MS Excel MVP

wrote in message

...

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



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

Michael Hager