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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,663
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
4,663
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
4,663
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
4,663
Office Version
  1. 365
Platform
  1. MacOS
you are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,268
Members
416,963
Latest member
samfuge

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