Can I sum a vlookup?

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
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 came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
Great thanks,

Now how can I make #N/A disappear? I get this because sometimes thier is no item listed
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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.
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319

ADVERTISEMENT

=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
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
=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))
 

bucci35

Active Member
Joined
Jul 6, 2002
Messages
319
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.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
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).
 

Forum statistics

Threads
1,136,352
Messages
5,675,280
Members
419,559
Latest member
BraytonM

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