# Find row of last occurrence

#### mintz

##### Board Regular
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?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try to post Excel readable data, not images. Also, better not to discuss a non-working formula but specify the outcome you need.

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

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

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)))

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

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

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))))

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)

Replies
4
Views
969
Replies
5
Views
612
Replies
3
Views
383
Replies
3
Views
430
Replies
4
Views
313

1,196,155
Messages
6,013,759
Members
441,781
Latest member
Gian Carlos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back