Count based on multiple criteria - CHALLENGING EQUATION

[afountas21]

The attached workbook is a product inventory system. The goal is to track 2 things that would be stored in two separate sheets in the same workbook.

Sheet 1(named Product Schedule): List of products, the locations each product is available at, the dates of availability for each product

Sheet 2 (named Product Calendar): Complete list of locations, that shows the # of products available during each date at each location.


The top row of cells in the 2nd sheet are dates. So A1 = 1/1/2012 A2= 1/2/2012... etc

Under each date should be an equation that says something along the lines of:

count location if date range euqals or is between the start and end dates.

Please Help. If you need further insight please message me. Would be greatly appreciated if someone could solve inside workbook and send back to me.

Thanks,

[TMS]

B3: =SUMPRODUCT(--('Product Schedule'!$B$3:$B$22=$A3),--('Product Schedule'!$C$3:$C$22=B$2))

Copy across and down


Regards, TMS

[Marcol]

Hmm? I make it ...

=SUMPRODUCT(('Product Schedule'!$B$3:$B$50=$A3)*(B$2>='Product Schedule'!$C$3:$C$50)*(B$2<='Product Schedule'!$D$3:$D$50))

Have I had a beer to many tonight ...