| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to sort usage of rented rooms on monthly basis? | Charts and Charting in Excel | |||
| Finding Monthly Usage | Excel Worksheet Functions | |||
| How can I take my monthly bar and kitchen inventory? | Excel Discussion (Misc queries) | |||
| Making list of items to truck monthly usage | Excel Discussion (Misc queries) | |||
| How do I create a spreadsheet to track monthly inventory | New Users to Excel | |||
Hybrid Mode
