Snasui Excel Forum

Hi dear,

I need your input how to calculate FiFo in excel using formula .I attach a sample with output in color cells and how to calculate amount in the next column as well.

Thanks

..

Hi Guy

Not sure what the point is that you need to fill out.

Try the results as attached, if it's not suited to your question, please give more clarification somehow to get that.

F2
=SUMPRODUCT((A2=$K$6:$K$21)*$L$6:$L$21)

H2
=SUMPRODUCT((A2=$K$6:$K$21)*$L$6:$L$21*$M$6:$M$21)


Norkaz

Thanks but the date in SALES!A:A not $K$6:$K$21

..

Refer to your attachment, if the date in SALES!A:A, it's quite not possible to get the results that I provided.

Please give more clarification If I've missed out on something that would be useful to you.

Norkaz

FIFO method is calculate for First On First Out,it means goods purchased first Sold out first

...

I know the FIFO method but I would like to know more if my answers matched your requirement or not.

If yes, it would be great.

If not, please explain why not, give more sample answers as it would be.

Norkaz

your answer is correct but I don't need helper column in K column.is it possible?

...

As the FIFO method, It's quite not possible if don't have the "Exactly Date" referring Sales Qty. in L:L that matches the date in A:A.

Norkaz

Try
F2
=MIN(C2,SUMIFS(SALES!$C$2:$C$25,SALES!$B$2:$B$25,$B2)-SUMIFS(F$1:F1,B$1:B1,B2))

H2
=SUM(TEXT(IF(SUMIFS(F$2:F2,B$2:B2,B2)-MMULT(--(ROW(SALES!$B$2:$B$25)>TRANSPOSE(ROW(SALES!$B$2:$B$25))),SALES!$C$2:$C$25*(SALES!$B$2:$B$25=B2))<SALES!$C$2:$C$25,SUMIFS(F$2:F2,B$2:B2,B2)-MMULT(--(ROW(SALES!$B$2:$B$25)>TRANSPOSE(ROW(SALES!$B$2:$B$25))),SALES!$C$2:$C$25*(SALES!$B$2:$B$25=B2)),SALES!$C$2:$C$25),"0;\0")*SALES!$D$2:$D$25*(SALES!$B$2:$B$25=B2))-SUMIFS(H$1:H1,B$1:B1,B2)

Ctrl+Shift+Enter

Thank bo_ry