# Can I sum a vlookup?

#### bucci35

##### Active Member
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### NBVC

##### Well-known Member
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
Great thanks,

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

#### NBVC

##### Well-known Member
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
=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
=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
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
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).

Replies
5
Views
92
Replies
1
Views
86
Replies
3
Views
511
Replies
8
Views
99
Replies
2
Views
132

1,171,163
Messages
5,874,125
Members
433,026
Latest member
LawnmowerITGuy

### 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.

### Which adblocker are you using?

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

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