Need a formula for inventory
Dear Techies,
Consider my input table (INWARD QTY) is
And i m looking for an output as shown in the table (STOCK SUMMARY) below.
I tried these formulae, but couldn’t achieve the output table values. Can anyone of you check and guide me how to do it.
=IF(InwardQty[Item Name]=[@[Item Name]],SUM(XLOOKUP(‘Stock Summary’!$H$2,InwardQty[#Headers],InwardQty):XLOOKUP(‘Stock Summary’!$J$2,InwardQty[#Headers],InwardQty)),””)
=XLOOKUP([@[Item Name]],InwardQty[Item Name],SUM(XLOOKUP(‘Stock Summary’!$H$2,InwardQty[#Headers],InwardQty[[1-Jun-24]:[31-Jul-24]]):XLOOKUP(‘Stock Summary’!$J$2,InwardQty[#Headers],InwardQty[[1-Jun-24]:[31-Jul-24]])),””,0,1)
=SUMPRODUCT((InwardQty[[#Headers],[1-Jun-24]:[31-Jul-24]]>=’Stock Summary’!$H$2)*(InwardQty[[#Headers],[1-Jun-24]:[31-Jul-24]]<=’Stock Summary’!$J$2)*(InwardQty[@[1-Jun-24]:[31-Jul-24]]))
=sumifs(InwardQty[[1-Jun-24]:[31-Jul-24]],InwardQty[[#Headers],[1-Jun-24]:[31-Jul-24]],”>=”&$E$1,InwardQty[[#Headers],[1-Jun-24]:[31-Jul-24]],”<=”&$E$2,InwardQty[Item Name]=InwardQty[@[Item Name]])
Thanks in advance.
Kalyan, Hyderabad, India.
Dear Techies, Consider my input table (INWARD QTY) is And i m looking for an output as shown in the table (STOCK SUMMARY) below. I tried these formulae, but couldn’t achieve the output table values. Can anyone of you check and guide me how to do it. =IF(InwardQty[Item Name]=[@[Item Name]],SUM(XLOOKUP(‘Stock Summary’!$H$2,InwardQty[#Headers],InwardQty):XLOOKUP(‘Stock Summary’!$J$2,InwardQty[#Headers],InwardQty)),””) =XLOOKUP([@[Item Name]],InwardQty[Item Name],SUM(XLOOKUP(‘Stock Summary’!$H$2,InwardQty[#Headers],InwardQty[[1-Jun-24]:[31-Jul-24]]):XLOOKUP(‘Stock Summary’!$J$2,InwardQty[#Headers],InwardQty[[1-Jun-24]:[31-Jul-24]])),””,0,1) =SUMPRODUCT((InwardQty[[#Headers],[1-Jun-24]:[31-Jul-24]]>=’Stock Summary’!$H$2)*(InwardQty[[#Headers],[1-Jun-24]:[31-Jul-24]]<=’Stock Summary’!$J$2)*(InwardQty[@[1-Jun-24]:[31-Jul-24]])) =sumifs(InwardQty[[1-Jun-24]:[31-Jul-24]],InwardQty[[#Headers],[1-Jun-24]:[31-Jul-24]],”>=”&$E$1,InwardQty[[#Headers],[1-Jun-24]:[31-Jul-24]],”<=”&$E$2,InwardQty[Item Name]=InwardQty[@[Item Name]]) Thanks in advance. Kalyan, Hyderabad, India. Read More
