Rolling Total for Each Warehouse Inventory

[petrodude]

Help! I'm stuck.

I am trying to create a rolling total for each of my 13 warehouses' on hand inventory at the end of each month in Column I

The idea is that if, for a row,

Item = Item

Month – 1 = Month
(Month "0" is the starting inventory at the beginning of the year, Month "1" is January, etc)

Whs = Whs

Then, SUM Net Inventory Cells

Else, ‘’


Here are some screenshots of sample data:
Data filtered for Whs "300" and Item

Data Filtered just for Item

Thank you in advance!

 

[petrodude]

You probably didn't investigate the "Known XL2BB Issues" near the top of the instructions page. One of those would lead you to this page which most likely will solve that issue.

Thank you for your quick response @Peter_SSs. I actually did find follow the steps you suggest and "unblocked" before you posted your reply and I am still experiencing issues.

Additionally, it appears the only options I have when posting are "Upload Image" or "Post Reply"

 

[Peter_SSs]

"Unblock" was only one part of what was in that thread. Did you also deal with the 'Trusted Location' issue?
If you did, and still have problems, did you also try the 'Alternative Installation: Excel Startup folder: XLSTART' section in the main instructions page?

 

[petrodude]

"Unblock" was only one part of what was in that thread. Did you also deal with the 'Trusted Location' issue?
If you did, and still have problems, did you also try the 'Alternative Installation: Excel Startup folder: XLSTART' section in the main instructions page?

Yes sir, I have done all of that. I am still having problems.

 

[Peter_SSs]

Yes sir, I have done all of that. I am still having problems.

Those things have solved issues for virtually everybody else.
I don't have any further suggestions, other than perhaps go through the uninstall process listed in the instructions then reinstall.

 

[SanjayGMusafir]

@petrodude What is the name of the table that you are working with? Let's think of some solution to your running total issue...

 

[SanjayGMusafir]

Let's start with the easiest thing first. Try this and revert -

Assuming your table starts from Cell A1 use this formula in I2 and below -

=IFS(C2=0,"",True,SUMIFS($H$1:H2,$C$1:C2,C2-1,$D$1:D2,D2,$E$1:E2,E2))

 

[petrodude]

@SanjayGMusafir - thank you for your willingness to help out. I still cannot figure out why the xl2bb isn't working. I have even had a friend look at this as well with no success.

Anyway, The formula you suggested still isn't quite perfect.

you can see your formula in the bar at the top. I have also added what the expected net inventory (column H) should be for item #156 in column I for warehouse #300. Month 0 (aka starting inventory on 1/1/23) is 169 units. Through January, month change (units in - units out) was -107. So the inventory at the end of January should be 169-107 = 62. For February, we started with those 62 units and month change was 30 so net inventory is 62+30 = 92 ... etc.


I did attempt to change the formula to reflect the starting inventory for month 0 but it still doesn't give me the proper values for column H (Net inventory)
=IFS(B22860=0,[@[Month Δ]],TRUE,SUMIFS($G$1:G22860,$B$1:B22860,B22860-1,$C$1:C22860,C22860,$D$1:D22860,D22860))

Any tweaks and suggestions would be much appreciated.

 

[SanjayGMusafir]

The formula was built on the basis of your condition in OP "Month - 1= Month"

But for the intended results, there has to ba modification to the formula

=IFS(C2=0,"",True,SUMIFS($G$1:G2,$B$1:B2,"<="&B2-1,$C$1:C2,C2,$D$1:D2,D2))

 

[SanjayGMusafir]

Check this

I edited it many times

=IFS(C2=0,"",True,SUMIFS($G$1:G2,$B$1:B2,"<="&B2-1,$C$1:C2,C2,$D$1:D2,D2))

 

[petrodude]

Check this

I edited it many times

=IFS(C2=0,"",True,SUMIFS($G$1:G2,$B$1:B2,"<="&B2-1,$C$1:C2,C2,$D$1:D2,D2))

It appears that the net inventory is lagging behind by 1 month when using this formula?

when using =

=IFS(C2=0,"",True,SUMIFS($G$1:G2,$B$1:B2,"<="&B2-1,$C$1:C2,C2,$D$1:D2,D2))

i get this error message