Suggested formula?

[IanBr]

Me again,

I have a table along the lines of:[TABLE="width: 500"]
<tbody>[TR]
[TD]a
[/TD]
[TD]1[/TD]
[TD]123[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]1[/TD]
[TD]34[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]1[/TD]
[TD]235423[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]1[/TD]
[TD]2435[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]2[/TD]
[TD]214[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]3[/TD]
[TD]2354[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD]2[/TD]
[TD]2352[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Where first column is an account number, the second is the sequence of the sale and the 3rd is the value of the sale. What I want to do is to have a formula in the 4th column along the lines of "if this is not the first sale, what was the value of the first sale"

So for example the returned value against the 5th line would be 123 (line 5 is the second sale to a and the first sale was for 123)

Any ideas?

I got as far as =if(a5>1,vlookup(a5,a1:c7,3,0),"nil") however this wouldn't necessarily pick up the first sale to "a" so there needs to be some sort of search to find the row where column a is a and column b is 1 and return value from column c of this row

 

[Eric W]

Try:

	A	B	C	D	E	F

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]123[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]34[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]c[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]235423[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]d[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2435[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]a[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]214[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"]123[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]3[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2354[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"][/TD]
[TD="align: right"]34[/TD]
[TD="align: right"][/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] "]7[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]b[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FAFAFA]#FAFAFA[/URL] , align: right"]2352[/TD]
[TD="align: right"][/TD]
[TD="align: right"]34[/TD]

</tbody>

Sheet8​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=IF(B1=1,"",SUMIFS($C$1:$C$10,$A$1:$A$10,A1,$B$1:$B$10,1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

How about
=IF(B1=1,"nil",SUMPRODUCT((A$1:A$7=A1)*(B$1:B$7=1),(C$1:C$7)))

 

[IanBr]

Thank you both.

Both of the above work!

Next step... can it be adapted so that it only returns values for each customer on one line so for example in Eric W's solution shown above, lines 6 and 7 return the answer 34 (correctly). Ideally I only want to see first spend value against the most recent sale (i.e in the above row 6 would return 34, line 7 would return blank)

Thanks again

 

[Fluff]

How about
=IF(OR(B1=1,COUNTIF(A$1:A$7,A1)<>B1),"",SUMPRODUCT((A$1:A$7=A1)*(B$1:B$7=1),(C$1:C$7)))

 

[Eric W]

Or:

=IF(COUNTIF($A$1:$A1,A1)<>2,"",SUMIFS($C$1:$C$10,$A$1:$A$10,A1,$B$1:$B$10,1))