Sum of a range of cells that include cells using LOOKUP
 

Don't know if I worded my subject correctly...anyhow:

I've made a spreadsheet listing employee benefits.

B C D
1 John 1
=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","77 .86"})
2 Mary 3
=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","77 .86"})

So, in John's column B) case, he is covering only one person in his
benefits(column C), so his premium (column D) would be 40.55; and Mary is
covering 3 people in her benefits, so her premium would be 130.34; etc...

Columns E - F are text (notes).

Column G contains various adjustments (not every employee has adjustments)
to the month's particular bill. For example, John may have been credited
(20.55) this month because he was overcharged the previous month. Mary,
however, has no adjustments.

Column H will total the monthly premium charge of the respective employee
(including charges and adjustments)--so the formula in column H will be
=sum(D1:G1) for John, but since Mary has no adjustments, H2 will be =D2.

Here is my problem: I am trying to sum H1:H57 and it is not working at all.
I should be getting the figure $2,499.59, but the formula is resulting in
$57.75 for some reason. I am thinking this has to do with the fact that
Column H depends upon a sum from Column D which utilizes the LOOKUP function.
How can I get an total for the actual values in Column H (without having to
individually click in each individual cell, press F2, then press F9, then
press Enter)?

Sum of a range of cells that include cells using LOOKUP
 

Remove the double quotes from the formula...and try

=lookup(C1,{1,2,3,5},{40.55,77.86,130.34,77.86})

If this post helps click Yes
---------------
Jacob Skaria


"klocascio" wrote:

Don't know if I worded my subject correctly...anyhow:

I've made a spreadsheet listing employee benefits.

B C D
1 John 1
=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","77 .86"})
2 Mary 3
=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","77 .86"})

So, in John's column B) case, he is covering only one person in his
benefits(column C), so his premium (column D) would be 40.55; and Mary is
covering 3 people in her benefits, so her premium would be 130.34; etc...

Columns E - F are text (notes).

Column G contains various adjustments (not every employee has adjustments)
to the month's particular bill. For example, John may have been credited
(20.55) this month because he was overcharged the previous month. Mary,
however, has no adjustments.

Column H will total the monthly premium charge of the respective employee
(including charges and adjustments)--so the formula in column H will be
=sum(D1:G1) for John, but since Mary has no adjustments, H2 will be =D2.

Here is my problem: I am trying to sum H1:H57 and it is not working at all.
I should be getting the figure $2,499.59, but the formula is resulting in
$57.75 for some reason. I am thinking this has to do with the fact that
Column H depends upon a sum from Column D which utilizes the LOOKUP function.
How can I get an total for the actual values in Column H (without having to
individually click in each individual cell, press F2, then press F9, then
press Enter)?

Sum of a range of cells that include cells using LOOKUP
 

Klocascio -

Try changing your formulas in column H to be D1 + G1 instead of
sum(D1:G1). Then your sum for these values in column H should be correct.

--
Daryl S


"klocascio" wrote:

Don't know if I worded my subject correctly...anyhow:

I've made a spreadsheet listing employee benefits.

B C D
1 John 1
=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","77 .86"})
2 Mary 3
=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","77 .86"})

So, in John's column B) case, he is covering only one person in his
benefits(column C), so his premium (column D) would be 40.55; and Mary is
covering 3 people in her benefits, so her premium would be 130.34; etc...

Columns E - F are text (notes).

Column G contains various adjustments (not every employee has adjustments)
to the month's particular bill. For example, John may have been credited
(20.55) this month because he was overcharged the previous month. Mary,
however, has no adjustments.

Column H will total the monthly premium charge of the respective employee
(including charges and adjustments)--so the formula in column H will be
=sum(D1:G1) for John, but since Mary has no adjustments, H2 will be =D2.

Here is my problem: I am trying to sum H1:H57 and it is not working at all.
I should be getting the figure $2,499.59, but the formula is resulting in
$57.75 for some reason. I am thinking this has to do with the fact that
Column H depends upon a sum from Column D which utilizes the LOOKUP function.
How can I get an total for the actual values in Column H (without having to
individually click in each individual cell, press F2, then press F9, then
press Enter)?

Sum of a range of cells that include cells using LOOKUP
 

=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","7 7.86"})

When you put quotes around numbers in formulas that turns them into TEXT.

Then if you try to use the SUM function on those results SUM will *ignore*
the results that are TEXT.

Try removing the quotes:

=LOOKUP(C1,{1,2,3,5},{40.55,77.86,130.34,77.86})

--
Biff
Microsoft Excel MVP


"klocascio" wrote in message
...
Don't know if I worded my subject correctly...anyhow:

I've made a spreadsheet listing employee benefits.

B C D
1 John 1
=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","77 .86"})
2 Mary 3
=lookup(C1,{1,2,3,5},{"40.55","77.86","130.34","77 .86"})

So, in John's column B) case, he is covering only one person in his
benefits(column C), so his premium (column D) would be 40.55; and Mary is
covering 3 people in her benefits, so her premium would be 130.34; etc...

Columns E - F are text (notes).

Column G contains various adjustments (not every employee has adjustments)
to the month's particular bill. For example, John may have been credited
(20.55) this month because he was overcharged the previous month. Mary,
however, has no adjustments.

Column H will total the monthly premium charge of the respective employee
(including charges and adjustments)--so the formula in column H will be
=sum(D1:G1) for John, but since Mary has no adjustments, H2 will be =D2.

Here is my problem: I am trying to sum H1:H57 and it is not working at
all.
I should be getting the figure $2,499.59, but the formula is resulting in
$57.75 for some reason. I am thinking this has to do with the fact that
Column H depends upon a sum from Column D which utilizes the LOOKUP
function.
How can I get an total for the actual values in Column H (without having
to
individually click in each individual cell, press F2, then press F9, then
press Enter)?