Find row of last occurrence

[mintz]

In Sheet1 I have a list of order records:

In Sheet2 I have a list of payment records:

In Sheet2, I need A10 to find the last paid order in the cells above it (i.e. 1033), then sum up the next order (1034) till the last order (1038) from Sheet1

something like
B10=SUM(Sheet1!ROW(1034):Sheet1!LASTROW)

How do I do that?

 

[Aladin Akyurek]

Try to post Excel readable data, not images. Also, better not to discuss a non-working formula but specify the outcome you need.

 

[mintz]

Try to post Excel readable data, not images. Also, better not to discuss a non-working formula but specify the outcome you need.

Yes just thought it would be easier to see what I'm trying from picture

Here's the data:

Order	Total
1031	11.00
1032	9.00
1033	21.00
1034	3.00
1035-1	8.00
1035-2	6.00
1036	5.00
1037	8.00
1038-1	5.00
1038-2	5.00
1038-3	5.00

<tbody>
</tbody>

Orders	Total
1000-1002	24.00
balance	6.00
1003-1008	65.00
1009-1021	90.00
1022-1024	34.00
balance	10.00
1025-1030	59.00
1031-1033	41.00
1034-1038	45.00

<tbody>
</tbody>

P.S. The paid column doesn't really matter

 

[WarfritLive]

Will it always be 4 digits? How about :
=SUMPRODUCT(--(VALUE(LEFT('Sheet 1'!A1:A5000))>=VALUE(RIGHT(A9,4))))

 

[Aladin Akyurek]

ADVERTISEMENT

In A10 of Sheet2 enter:

=RIGHT(A9,4)&"-"&LEFT(LOOKUP(REPT("z",255),Sheet1!A:A),4)

In B10 of Sheet2 control+shift+enter, not just enter:

=SUM(INDEX(Sheet1!B:B,MATCH(RIGHT(A9,4),LEFT(Sheet1!A:A,4),0)+1):INDEX(Sheet1!B:B,MATCH(REPT("z",255),Sheet1!A:A)))

 

[mintz]

In A10 of Sheet2 enter:

=RIGHT(A9,4)&"-"&LEFT(LOOKUP(REPT("z",255),Sheet1!A:A),4)

In B10 of Sheet2 control+shift+enter, not just enter:

=SUM(INDEX(Sheet1!B:B,MATCH(RIGHT(A9,4),LEFT(Sheet1!A:A,4),0)+1):INDEX(Sheet1!B:B,MATCH(REPT("z",255),Sheet1!A:A)))

It seems to work great when the last order in Sheet1 has a "-" in the middle, but if it doesn't include a "-" the order doesn't get counted, how do I go around that?
e.g. when I added 1038-4 it counted it but when I added 1039 it just counted until 1038-4

 

[mintz]

ADVERTISEMENT

One exception case, when the row above is anything other than xxxx-xxxx (e.g. "balance") it needs to lookup the row above till it finds a xxxx-xxxx combination

ok:

error:

btw it can be a UDF if it's impossible to achieve with in-cell formulas

 

[Aladin Akyurek]

It seems to work great when the last order in Sheet1 has a "-" in the middle, but if it doesn't include a "-" the order doesn't get counted, how do I go around that?
e.g. when I added 1038-4 it counted it but when I added 1039 it just counted until 1038-4

In A10 of Sheet2 enter:

=RIGHT(A9,4)&"-"&LEFT(LOOKUP(9.99999999999999E+307,SEARCH("?",Sheet1!A:A),Sheet1!A:A),4)

In b10 of Sheet2 control+shift+enter, not just enter:

=SUM(INDEX(Sheet1!B:B,MATCH(RIGHT(A9,4),LEFT(Sheet1!A:A,4),0)+1):INDEX(Sheet1!B:B,MATCH(9.99999999999999E+307,SEARCH("?",Sheet1!A:A))))

 

[Aladin Akyurek]

One exception case, when the row above is anything other than xxxx-xxxx (e.g. "balance") it needs to lookup the row above till it finds a xxxx-xxxx combination

ok:

error:

btw it can be a UDF if it's impossible to achieve with in-cell formulas

Modify A10 to:

=RIGHT(LOOKUP(9.99999999999999E+307,SEARCH("????-????",$A$2:A9),$A$2:$A9),4)&"-"&LEFT(LOOKUP(9.99999999999999E+307,SEARCH("?",Sheet1!A:A),Sheet1!A:A),4)