SUMIF - last n number of rows

Joined
Jun 13, 2017
Messages
108
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 DCOLUMN ECOLUMN FCOLUMN RCOLUMN S
ClientTransactionQuantityOrder ID (always unique number)Final Price (formula)
Company1Purchase6J512f3151f1sfhg4t23r-600
Company1Sale-6OIHKLNhbu2g8ihor660
Company1Purchase56*(TH"OIGNEP6D-5600
Company2Purchase-12LGPNJWHEI4GHjdkn1200
Company2Sale12IPHEIL£n4kkrENG1800
Company1Purchase1IOH£*g23fgopj100

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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

lokayee

New Member
Joined
Jan 3, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Capture.JPG
 
Joined
Jun 13, 2017
Messages
108
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,489
Messages
5,636,626
Members
416,931
Latest member
pattichis

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top