Sumproduct and dates

I

Ian Westwell

One final question on counting....

Column B has "date" enterred eg 24 Mar 03. how can I
search and count the occurrance in other columns between
two set dates ie the amount of times data in column E
matches data in column C between 1/4/03 and 30/4/03.

Searching between dates has been a problem using other
functions such as SUM, Count, etc

=SUMPRODUCT((data!B2:B2500......Between dates...)*(data!
E2:E2500="y")*(data!C2:C2500="z"))
 
D

Debra Dalgleish

You can enter your start and end dates on the worksheet, and refer to
those cells in the SUMPRODUCT formula. For example, with start date in
cell B2, and end date in cell B3:


=SUMPRODUCT((Data!B2:B2500>=B2)*(Data!B2:B2500<=B3)*(Data!E2:E2500="y")*(Data!C2:C2500="z"))
 
Top