summing a Vlookup column

dvmclau

New Member
Joined
Feb 8, 2019
Messages
4
I have a vlookup column that I would like to sum the dollar amounts that vlookup puts in the column.
Thanks
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,714
Office Version
  1. 365
Platform
  1. MacOS
i need a little more detail to understand what you are trying to do exactly
 

dvmclau

New Member
Joined
Feb 8, 2019
Messages
4
i need a little more detail to understand what you are trying to do exactly

The Vlookup is set up in a column that shows me the dollar amount for the code which is put in the A column. Table for Vlookup is set up in the Excel spreadsheet. The problem is I can't sum the column due to the calculation remaining in the cells. I don't know if I have missed something in the calculation or in the summing. Or do I need to have the result of the calculation put in another column? I'm totally lost with trying to fix this.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,714
Office Version
  1. 365
Platform
  1. MacOS
sorry still dont get it
The problem is I can't sum the column due to the calculation remaining in the cells.
what column and what calculation

lets assume the value from vlookup is in column B based on values from column A
what do you want to sum
sum(B:B) should work - if your lookup table has numbers in and are returning those $ values as numbers
 

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184

ADVERTISEMENT

Could your VLOOKUP be returning a text value? If so, you could try
=VALUE(VLOOKUP(......))
 

dvmclau

New Member
Joined
Feb 8, 2019
Messages
4
sorry still dont get it


what column and what calculation

lets assume the value from vlookup is in column B based on values from column A
what do you want to sum
sum(B:B) should work - if your lookup table has numbers in and are returning those $ values as numbers[/Q

This is the calculation that is in the column =VLOOKUP(A2467,$U$4:$V$6,2,FALSE) this is what is returned to the column 45.00[FONT=Arial, sans-serif][COLOR=rgba(0, 0, 0, 0.847059)] [/COLOR][/FONT]
and the Vlookup calculation remains in the column too. Not every cell has a dollar amount in it being there is not code entered in column A. So with just the calculation left in and no code entered in A column it just gives the #N/A.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,714
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

yes .thats the correct response from vlookup when it cannot find a match

so you have a column with
45.00
#N/A

and you want to sum()
and you get #N/A in the result because of the errors

if you modified the vlookup()
=IFERROR(VLOOKUP(A2467,$U$4:$V$6,2,FALSE) ,"" )
Now instead of #N/A you get a blank cell
and now the SUM() will work
 

dvmclau

New Member
Joined
Feb 8, 2019
Messages
4
yes .thats the correct response from vlookup when it cannot find a match

so you have a column with
45.00
#N/A

and you want to sum()
and you get #N/A in the result because of the errors

if you modified the vlookup()
=IFERROR(VLOOKUP(A2467,$U$4:$V$6,2,FALSE) ,"" )
Now instead of #N/A you get a blank cell
and now the SUM() will work

Thank you, thank you! It worked
Wish you the best.
 

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
5,714
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,484
Messages
5,837,602
Members
430,505
Latest member
DevAlex

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top