Thread: Calculating work-in-process

Greetings,

I am trying to come up with a way to calculate the number of items in-work on every date in a given date range. I'm going to make this generic, but basically, I'm in a manufacturing environment -- different products go into work on different dates, and the # of days in-process can vary from product to product. I want to construct a formula that identifies the number of products that will be in WIP (work-in-process) on each day in the given time frame.

I've attached a sample spreadsheet demonstrating what I'm looking for, including some sample data. Any help would be appreciated.

Thanks!

Welcome to the forum!

Here's one method:

Code:

=SUMPRODUCT(--($B$2:$B$7<=A10),--($C$2:$C$7>=A10))

Amend ranges as necessary and copy down.

Cheers,

Given use of XL2007 you could also use:

=COUNTIFS($B$2:$B$7,"<="&$A10,$C$2:$C$7,">="&$A10)

If the "parts" range is much bigger than your example I'd advise COUNTIFS but note, unlike SUMPRODUCT, it's not backwards compatible with earlier versions of XL.