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]

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

 

[SanjayGMusafir]

One more try

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

 

[petrodude]

It is still behind by 1 month

 

[petrodude]

My mistake

Check this -

Book1
	A	B	C	D	E	F	G	H
1	Year	Month	Whs	Item	In	Out	Month	Net
2	2023	0	300	156			169
3	2023	1	300	156	1	108	-107	62
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=IFS(B2=0,"",TRUE,SUMIFS($G$1:G2,$B$1:B2,"<="&B2,$C$1:C2,C2,$D$1:D2,D2))

Eureka! It appears to be working now.

This is amazing. Thank you so so much!

 

[SanjayGMusafir]

Finally after a series of Trial and Error on my part

Glad to help you and thanks for the feedback.