Sum back across multiple rows to get to set value

W

Waspeze

New Member
Joined
Nov 15, 2017
Messages
1
Hi,
I have a table of data showing when quantities of stock have been received (columns C-N) and the total stock remaining of each item. Unfortunately some of the items have multiple lines due to different vendors.
What I need is a formula (or VBA if not possible with a formula) that can take give the total stock for each item taking into account the possible multiple lines.
So in the example attached, most have only one line so not a problem but one item has two lines and the formula would need to produce the results in column P, where it finds the latest stock receipt and then sums back until the total is reached.

Item aaaa vendor a would be 698+1804+8+(3018-698-1804-8-187) = 2831
item aaaa vendor g would be 187

in the main file, not the example attached, there are a few thousand lines and potentially up to 3 occurrences per item

Really struggling with how to get the results required so will be grateful for any assistance!


[TABLE="width: 836"]
<tbody>[TR]
[TD="width: 70, bgcolor: transparent"][/TD]
[TD="width: 51, bgcolor: transparent"][/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Nov-16
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Dec-16
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Jan-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Feb-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Mar-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Apr-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]May-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Jun-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Jul-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Aug-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Sep-17
[/TD]
[TD="class: xl64, width: 61, bgcolor: transparent, align: right"]Oct-17
[/TD]
[TD="width: 141, bgcolor: transparent"][/TD]
[TD="width: 116, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Item Class
[/TD]
[TD="class: xl63, bgcolor: transparent"]vendor
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Quantity
[/TD]
[TD="class: xl63, bgcolor: transparent"]Oct Stock Qty by item
[/TD]
[TD="class: xl63, bgcolor: transparent"]Oct true stock qty
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow"]aaaaa
[/TD]
[TD="class: xl65, bgcolor: yellow"]a
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]3313
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]1804
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]698
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]3018
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]2831
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]bbbb
[/TD]
[TD="bgcolor: transparent"]b
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]200
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]1
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]1
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ccccc
[/TD]
[TD="bgcolor: transparent"]c
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]200
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]84
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]84
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]ddddd
[/TD]
[TD="bgcolor: transparent"]d
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]200
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]eeeee
[/TD]
[TD="bgcolor: transparent"]e
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]200
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]206
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]206
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]fffff
[/TD]
[TD="bgcolor: transparent"]f
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]188
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]0
[/TD]
[TD="bgcolor: transparent, align: right"]39
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]39
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: yellow"]aaaaa
[/TD]
[TD="class: xl65, bgcolor: yellow"]g
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]263
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]187
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]0
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]3018
[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]187
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
You must log in or register to reply here.

Similar threads

T
  • Question Question
SUMPRODUCT Over Multiple Columns
Replies
2
Views
273
thewiseguy
T
C
  • Question Question
Need help with look up formula
Replies
5
Views
667
GuzmanServices
G
A
  • Question Question
create report for 40000 rows to calculation stock across sheet based on sheet name in cell value
Replies
1
Views
367
Amer Omar
A
N
  • Question Question
Easiest way to get total.
Replies
3
Views
488
awoohaw
awoohaw
H
  • Question Question
Having trouble calendarizing a utility bill across multiple months. Is there a quicker method than what I've done so far?
Replies
1
Views
654
helnuma2k
H

Forum statistics

Threads
1,224,584
Messages
6,179,693
Members
452,938
Latest member
babeneker

Share this page

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
Back
Top