Sum the results of a vlookup

[Gino59]

Hi y'all...

Been looking all over but no luck! I have a column of results from a
vlookup that works just fine. It basically takes a discount code number and
returns a dollar value (ie; code 1 returns $100, code 2 returns $50, etc.).

All I want to do at the bottom of that column is sum up the total $ value.
And all I get is #N/A...

There are no #N/A's returned in the vlookup cells, it's just when I try to
total the cells with what I though would be a simple =sum(e3:e30) that I get
the error.
(e3:e30 have the vlookup results).

I just know this is going to be one of the "D'oh" moments for me!
Suggestions??

Thanks all y'all!

 

[Teethless mama]

You can test each cell by entering this formula

In F3: =ISERROR(E3)
copy down to F30. If the formula returns TRUE which mean,that cell has an
error like #n/a. Either you can fix the error cell(s), or you can use this
formula

=SUMIF(E3:E30,"<1E100")

 

[Gino59]

Thx but all cells return FALSE. As for the sumif - I want to sum the $ value
of all the cells that show up in column F as a result of the vlookup
performed in each of those cells. In other words...
Cell F3 has =VLOOKUP(E3,'Tuition Codes'!$A$2:$C$9,3,FALSE) in it which shows
a $ amount (in this case say $100.00). Cell E3 in this case is a data
validation cell where a code number can be selected (say Code 01-Code 08).
When a code is entered in cell E3, the value in F3 changes. Cell F4 through
the rest of the column have the same vlookup formula and return different $
values based on the 'Tuition Codes' fields referenced by the vlookup (say,
$50.00, $34.86, etc.).

I select cells F3 through F10 and hit autosum - and then I get the #N/A error.

??

 

[Teethless mama]

Upload your file to this site: http://www.4shared.com/

 

[Gino59]

Thank you! File has been uploaded.
hcs
rev1.xlsx

 

[Ashish Mathur]

Hi,
I checked your file. There is a hidden row (row 9) which contains an error
value - this is why the sum is not working. Try this formula

=SUMIF(F3:F11,">0")+SUMIF(F3:F11,"<0")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Thank you! File has been uploaded.
hcs
rev1.xlsx

Upload your file to this site: http://www.4shared.com/

 

[Gino59]

Wow - thanks Ashish! I knew this was going to be a big "D'oh" on my part! I
never noticed row 9 being hidden. A normal "sum" works perfectly as does the
sumif statement you provided!

Thank you Ashish and Teethless Mama!!!

Hi,
I checked your file. There is a hidden row (row 9) which contains an error
value - this is why the sum is not working. Try this formula

=SUMIF(F3:F11,">0")+SUMIF(F3:F11,"<0")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

Thank you! File has been uploaded.
hcs
rev1.xlsx

Upload your file to this site: http://www.4shared.com/


:

Thx but all cells return FALSE. As for the sumif - I want to sum the $
value
of all the cells that show up in column F as a result of the vlookup
performed in each of those cells. In other words...
Cell F3 has =VLOOKUP(E3,'Tuition Codes'!$A$2:$C$9,3,FALSE) in it which
shows
a $ amount (in this case say $100.00). Cell E3 in this case is a data
validation cell where a code number can be selected (say Code 01-Code
08).
When a code is entered in cell E3, the value in F3 changes. Cell F4
through
the rest of the column have the same vlookup formula and return
different $
values based on the 'Tuition Codes' fields referenced by the vlookup
(say,
$50.00, $34.86, etc.).

I select cells F3 through F10 and hit autosum - and then I get the #N/A
error.

??

:

You can test each cell by entering this formula

In F3: =ISERROR(E3)
copy down to F30. If the formula returns TRUE which mean,that cell
has an
error like #n/a. Either you can fix the error cell(s), or you can use
this
formula

=SUMIF(E3:E30,"<1E100")




:

Hi y'all...

Been looking all over but no luck! I have a column of results from
a
vlookup that works just fine. It basically takes a discount code
number and
returns a dollar value (ie; code 1 returns $100, code 2 returns
$50, etc.).

All I want to do at the bottom of that column is sum up the total $
value.
And all I get is #N/A...

There are no #N/A's returned in the vlookup cells, it's just when I
try to
total the cells with what I though would be a simple =sum(e3:e30)
that I get
the error.
(e3:e30 have the vlookup results).

I just know this is going to be one of the "D'oh" moments for me!
Suggestions??

Thanks all y'all!