sum with new vlookup sheet reference

DV

I have this formula copied down a column: =vlookup(A1,ReferenceAnotherSheet,3). The first row bring back the correct number value. The other rows are bringing back a #nomatch.



Also, I want the cell to return the total of all cells in column 3 that contain the cell referenced in A1. I know I need to use sum or sumif, but can't figure it out. Thanks.

Jim Hook

DV, without being able to see both entire sheets it's difficult to understand why only the first row gets a good match. Also, I can't see what is in A1 for the search value. If you copied and pasted the same formula in all the cells below the next row would look for a match to whatever's in A2, and do on. In your formula you have to make sure that row 1 of the external lookup table has all the values you're searching for in it and that row three of the external reference table has all the results you want back.

The SUM function simply sums all the values in the range of cells selected including a range on another sheet. SUMIF sums the same way but only sums values that meet a criterion, something like values that are >100 for example. I prefer to use the SUMIFS function since it works the same for one or more criteria.

When I start using a new advanced function I usually do some simple experiments on a sheet where it is easier to see what's happening and how it works. Once you get it figured out it's easier to use it in more complex situations.