# Thread: Search a data set and return adjacent cell values and sum them within one cell

1. ## Search a data set and return adjacent cell values and sum them within one cell

Hi all,

I have two tables below which form a much bigger data set, I am trying to create a search of teh first table Col A, 52,53,54 etc. and return and sum all found instances in Col B. As you can see I have set up a search and it returns only the first instance it finds (i.e. 54 returns 19.40) it should return 38.8. I created a help colum to round down the original data set so i could match the data more easily. I am pretty stumped at the moment and hoping someone can help identify a better way to do this.

If you need any more info let me know, I am continuing to work on it and will post a solution if i find one.

Col B formula in first table:
=SUMPRODUCT(VLOOKUP(A3,\$B\$8:\$C\$13,2,0))

Col A Col B
 52 #N/A 53 #N/A 54 19.40 55 #N/A 69 41.50 Col A Col B Col C 54.9788 54 19.40 54.9982 54 19.40 69.9443 69 41.50 69.9858 69 41.50 70.7327 70 16.80 70.7495 70 16.80

2. Perhaps =SUMPRODUCT((\$B\$8:\$B\$13=\$A3)*\$C\$8:\$C\$13)

3. or:
=SUMIF(\$B\$8:\$B\$13,A3,\$C\$8:\$C\$13)

4. ## Thanks

Thanks, sumif has done the trick!