|
Calculate Monthly Inventory Usage
I'm trying to calculate monthly inventory usage from data retrieved from
daily usage reports. The formula below works, but not sure how to ignore rows with receiving data. Column "C" includes notes and I would like to ignore in my calculation all rows with the note of "Received against PO". How can this be accomplished? The formula I'm currently using is... =SUMIF($H$2:$H$100,"1/2009",$D$2:$D$100) Column "H" has the month/year and column "D" includes both usage and receipts. Thanks in advance! Brian |
Calculate Monthly Inventory Usage
try
=SUMproduct(($H$2:$H$100="1/2009")*($c$2:$c$100<"Received against PO")*$D$2:$D$100) -- Don Guillett Microsoft MVP Excel SalesAid Software "Brian" wrote in message ... I'm trying to calculate monthly inventory usage from data retrieved from daily usage reports. The formula below works, but not sure how to ignore rows with receiving data. Column "C" includes notes and I would like to ignore in my calculation all rows with the note of "Received against PO". How can this be accomplished? The formula I'm currently using is... =SUMIF($H$2:$H$100,"1/2009",$D$2:$D$100) Column "H" has the month/year and column "D" includes both usage and receipts. Thanks in advance! Brian |
Calculate Monthly Inventory Usage
Thanks Don, but this returns the same result and does not ignore the quantity
that was received. "Don Guillett" wrote: try =SUMproduct(($H$2:$H$100="1/2009")*($c$2:$c$100<"Received against PO")*$D$2:$D$100) -- Don Guillett Microsoft MVP Excel SalesAid Software "Brian" wrote in message ... I'm trying to calculate monthly inventory usage from data retrieved from daily usage reports. The formula below works, but not sure how to ignore rows with receiving data. Column "C" includes notes and I would like to ignore in my calculation all rows with the note of "Received against PO". How can this be accomplished? The formula I'm currently using is... =SUMIF($H$2:$H$100,"1/2009",$D$2:$D$100) Column "H" has the month/year and column "D" includes both usage and receipts. Thanks in advance! Brian |
Calculate Monthly Inventory Usage
SUMPRODUCT((YEAR($A$2:$A$1000)=$V15)*(MONTH($A$2:$ A$1000)=COLUMN(A7))*(TRIM($C$2:$C$1000)<$Q$13)*$D $2:$D$1000)
-- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your wb to my address below along with specific instructions and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Brian" wrote in message ... Thanks Don, but this returns the same result and does not ignore the quantity that was received. "Don Guillett" wrote: try =SUMproduct(($H$2:$H$100="1/2009")*($c$2:$c$100<"Received against PO")*$D$2:$D$100) -- Don Guillett Microsoft MVP Excel SalesAid Software "Brian" wrote in message ... I'm trying to calculate monthly inventory usage from data retrieved from daily usage reports. The formula below works, but not sure how to ignore rows with receiving data. Column "C" includes notes and I would like to ignore in my calculation all rows with the note of "Received against PO". How can this be accomplished? The formula I'm currently using is... =SUMIF($H$2:$H$100,"1/2009",$D$2:$D$100) Column "H" has the month/year and column "D" includes both usage and receipts. Thanks in advance! Brian |
Calculate Monthly Inventory Usage
The formula worked fine for what I asked, but I found there were some other
issues that causes problems for what I needed. The formula mutated to... =SUMPRODUCT((YEAR('2928'!$A$2:$A$1000)=$B$2)*(MONT H('2928'!$A$2:$A$1000)=COLUMN(A7))*(LEFT('2928'!$C $2:$C$1000,9)=$U$2)*'2928'!$D$2:$D$1000) The part of the formula above with ($U$2) needs to include both ($U$2) & ($U$3). If the range ('2928'!$A$2:$A$1000) equals either of the the cells ($U$2) or ($U$3), it will include the qty in the calculation. Any takers for this problem? Brian "Don Guillett" wrote: SUMPRODUCT((YEAR($A$2:$A$1000)=$V15)*(MONTH($A$2:$ A$1000)=COLUMN(A7))*(TRIM($C$2:$C$1000)<$Q$13)*$D $2:$D$1000) -- Don Guillett Microsoft MVP Excel SalesAid Software "Don Guillett" wrote in message ... If desired, send your wb to my address below along with specific instructions and before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Brian" wrote in message ... Thanks Don, but this returns the same result and does not ignore the quantity that was received. "Don Guillett" wrote: try =SUMproduct(($H$2:$H$100="1/2009")*($c$2:$c$100<"Received against PO")*$D$2:$D$100) -- Don Guillett Microsoft MVP Excel SalesAid Software "Brian" wrote in message ... I'm trying to calculate monthly inventory usage from data retrieved from daily usage reports. The formula below works, but not sure how to ignore rows with receiving data. Column "C" includes notes and I would like to ignore in my calculation all rows with the note of "Received against PO". How can this be accomplished? The formula I'm currently using is... =SUMIF($H$2:$H$100,"1/2009",$D$2:$D$100) Column "H" has the month/year and column "D" includes both usage and receipts. Thanks in advance! Brian |
| All times are GMT +1. The time now is 05:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com