How to Get Period-wise Stock

[krunal123]

@ How to Get Period-wise Stock

N3 =SUMIFS(E:E,C:C,L3,D:D,M3) ( IN)


O3 =SUMIFS(J:J,H:H,L3,I:I,M3) (OUT)

 

[SanjayGMusafir]

Just add another condition to your formula - That is the beauty of SUMIFS

=SUMIFS(E:E,C:C,L3,D:D,M3,B:B,">="$S$2",B:B,"<="&$U$2)

Try it

 

[SanjayGMusafir]

And far better way is pivot tables if you have start and end date exactly as Month Beginning & Month End. Else SUMIFS would do as told above.

 

[krunal123]

And far better way is pivot tables if you have start and end date exactly as Month Beginning & Month End. Else SUMIFS would do as told above.

i was make SALE & PURCHASE PIVOT TABLE ,

but i was fail for make this stock match with date wise in pivot table

 

[SanjayGMusafir]

i was make SALE & PURCHASE PIVOT TABLE ,

but i was fail for make this stock match with date wise in pivot table

Two things Krunal
If you want Pivot table to do everything you have to bring both inward and outward transaction in one table but quantities in seperate column - Just like we do for banking transactions.

Else did you try the formula I gave you above - that shall summarize all your values at one place with as many conditions you want to apply.

Try that

 

[SanjayGMusafir]

Look at this -

All Records.xlsb
	A	B	C	D	E	F	G	H	I	J	K
1	Date	Category	Product	Inward	Outward	Note		Summary
2	1-Jun	Flour	Corn Flour	20		Opening Bal		1-Jun	30-Jun
3	1-Jun	Fro veg	Methi	50		Opening Bal		Product	Net	Inward	Outward
4	1-Jun	Snacks	Namkeen	20		Opening Bal		Corn Flour	470	540	70
5	1-Jun	Flour	Rice Flour	30		Opening Bal		Methi	(50)	200	250
6	1-Jun	Flour	Corn Flour	520				Namkeen	510	840	330
7	1-Jun	Fro veg	Methi	150				Rice Flour	30	30	0
8	2-Jun	Snacks	Namkeen	820
9	6-Jul	Flour	Rice Flour	1,430
10	1-Jun	Flour	Corn Flour		70
11	4-Jun	Fro veg	Methi		250
12	9-Jun	Snacks	Namkeen		330
13	10-Jul	Flour	Rice Flour		650
Sheet1

Cell Formulas
Range	Formula
I4:I7	I4	=SUMIFS(StockAccount[Inward],StockAccount[Product],[@Product],StockAccount[Date],">="&$H$2,StockAccount[Date],"<="&$I$2)-SUMIFS(StockAccount[Outward],StockAccount[Product],[@Product],StockAccount[Date],">="&$H$2,StockAccount[Date],"<="&$I$2)
J4:J7	J4	=SUMIFS(StockAccount[Inward],StockAccount[Product],[@Product],StockAccount[Date],">="&$H$2,StockAccount[Date],"<="&$I$2)
K4:K7	K4	=SUMIFS(StockAccount[Outward],StockAccount[Product],[@Product],StockAccount[Date],">="&$H$2,StockAccount[Date],"<="&$I$2)

 

[SanjayGMusafir]

Krunal if the solution resolves your issue, please mark the thread as closed and the post with probable solution. It help others to reach the solution faster.

Thanks a lot

 

[krunal123]

Krunal if the solution resolves your issue, please mark the thread as closed and the post with probable solution. It help others to reach the solution faster.

Thanks a lot

#How to make this pivot table This type Need " Expenses" Summery Report in pivot table.

i was try but not showing this style.......
pleased help for this pivot table summery

(Query Purpose : if then 100 or more Party, Need party wise Different- Different Expenses use.... if then this Situation , How to making report for this type.....
Sir Kindly Understand and help me......again

*Pleased Help and Share Screen Shot ,
and Send Steps for how set data hader