Trying to use Sumif

[Cdshep15]

I am trying to use SUMIF to sum a number of items on one tab into one cell on the other.

I have a cell with a three series of numbers in one cell. In that same row, next cell, I would like to return values from another sheet which are in different rows.:

Sheet 1:

Column A column C
----------- ------------
Row 1 887989
987677 ????Formula????
876663 (all in one cell )


Sheet 2:
Column A Column B
----------- -----------
row 1 887989 1
row 2 987677 4
row 3 876663 3


Ideally, I would like that formula to return 8 as the answer.

 

[just_jon]

I'm not sure, at all, what you really want here -- what are the 'givens', and what should be returned?

 

[orbea_adam]

Who, What? I'm with just_jon on this one.

 

[pgc01]

Hi Cdshep15

I'm not sure I understand you question. This is my wild guessing:

You say that all the 3 numbers are in the same cell? What is the delimeter.

So in Sheet1!A1 you have something like

"887989 987677 876663"

And in Sheet1!B1 you would like a formula equivalent to

=VLOOKUP(887989,Sheet2!A:B,2,0)+VLOOKUP(987677,Sheet2!A:B,2,0)+VLOOKUP(876663,Sheet2!A:B,2,0)=1+4+3=8

Am I close?

Please elaborate.

Regards
PGC

 

[vane0326]

Here is one and many ways to do this.


Formula in cell B3:


=SUMPRODUCT(--(D3:D10=LEFT(A2,6)+0)+(D3:D10=MID(A2,7,7)+0)+(D3:D10=RIGHT(A2,6)+0),E3:E10)


Maybe someone could come up a better formula.

Book1
	A	B	C	D	E	F
1
2	887989 987677 876663
3		4
4
5				887989	1
6				987677	2
7				876663	1
8
9
10
11
Sheet1