Can I sum a vlookup?

bucci35

Active Member
Joined
Jul 6, 2002
Messages
341
Office Version
  1. 365
Platform
  1. Windows
I have a data base that has items listed in column A. In columns b,c,d,e,f I have number values. The data is about 700 rows long.
I wrote a vlookup that can find the value in any single column but what I want to do is look up a particular item then go across and sum up the values in b,c,d,e,f. Any ideas?

Thanks
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try:

=SUMPRODUCT((A1:A700=H1)*B1:F700)

where H1 houses the string to look up within column A

adjust ranges to suit as required.

Note: This assumes one occurance only of string in H1 within A....otherwise it will sum all corresponding values in all rows containing the string in H1 within column A
 
Upvote 0
Great thanks,

Now how can I make #N/A disappear? I get this because sometimes thier is no item listed
 
Upvote 0
I am not sure why you would get that error....my formula will return zero if the item does not appear in Column A or if no corresponding values are found in B to F.

What exactly is your situation that you think is giving the #N/A....are the values in B to F returned from a formula? If that is the case, then try:

=SUM(IF(A1:A700=H1,IF(ISNUMBER(B1:F700),B1:F700)))

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see {} brackets around the formula if entered correctly.
 
Upvote 0
=VLOOKUP(CONCATENATE($B$6,A8),$T$7:$AG$6008,14,0)

Sorry, this is different than original question
Now, this is a formula that I am using.
When thier is no item that the concatenate calculates it returns the #N/A, but when this item is in column t it works. I just need it to show blank instead of the #N/A
 
Upvote 0
=VLOOKUP(CONCATENATE($B$6,A8),$T$7:$AG$6008,14,0)

Sorry, this is different than original question
Now, this is a formula that I am using.
When thier is no item that the concatenate calculates it returns the #N/A, but when this item is in column t it works. I just need it to show blank instead of the #N/A

=IF(ISNA(MATCH($B$6&A8,$T$7:$T$6008,0)),"",VLOOKUP($B$6&A8,$T$7:$AG$6008,14,0))
 
Upvote 0
Wow, are you good!!!!!
Now, last question..I think.

in cell a1 the user will type a percentage
in cell r8 thier is a number
If the result of the above formula is greater than the number in r8*a1 I want the cell that the formula is in, which is b8 to be blank, otherwise show the number that the formula above originally gave.
 
Upvote 0
I think you mean:

=IF(OR(ISNA(MATCH($B$6&A8,$T$7:$T$6008,0)),VLOOKUP($B$6&A8,$T$7:$AG$6008,14,0)>R8*$A$1),"",VLOOKUP($B$6&A8,$T$7:$AG$6008,14,0))

I have assumed that A1 is Absolute (frozen) and R8 is Relative (dynamic) so that the formula can be copied down (or across).
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

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
Back
Top