# SUMIF - last n number of rows

#### Excelllllllllllllllllllll

##### Board Regular
Greetings!

I've recently been given a sizable chunk of my company's finance sheets to go through the processed payments only (bought X qty - sold X qty)
I can do this manually, but am having a trouble creating a formula or pivot that can help me with this.

 COLUMN D COLUMN E COLUMN F COLUMN R COLUMN S Client Transaction Quantity Order ID (always unique number) Final Price (formula) Company1 Purchase 6 J512f3151f1sfhg4t23r -600 Company1 Sale -6 OIHKLNhbu2g8ihor 660 Company1 Purchase 56 *(TH"OIGNEP6D -5600 Company2 Purchase -12 LGPNJWHEI4GHjdkn 1200 Company2 Sale 12 IPHEIL£n4kkrENG 1800 Company1 Purchase 1 IOH£*g23fgopj 100

Basically I need a way to calculate the Purchase from row 1+ sale from row 2. Then ignore rows 3&6 completely until we sell 57 units (or 56 vs 56....and 1 vs 1 - because we buy each order separately so we wouldn't sell less than we bough, but we may sell all that we hold if it's for the same client (but this is only by request so it doesn't happen all the time)).

And then I need to do the same for Company 2.

SO - I need a formula or pivot for the above example that shows:
Company 1 = 60
Company 2 = 600

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

#### Excelllllllllllllllllllll

##### Board Regular
Thank you very much for the idea.
I'm actually doing something similar in having these as a table with a totals row on the bottom and filtering company1 them manually leaving only the finished transactions.

That's why I'm looking for a formula that can automate this process and self-populate (if you will) the Y on a helper column perhaps (like in your example)?
Where I'm falling short is when we buy 56 & 1 - then sell 57 - I can't tie those up to match through a formula, or indeed ignore the 56+1 before the sale.

Replies
5
Views
148
Replies
2
Views
1K
Replies
0
Views
535
Replies
3
Views
327
Replies
2
Views
180

1,129,372
Messages
5,635,880
Members
416,886
Latest member
coreyalaurence37

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