Sumproduct and dates

  • Thread starter Thread starter Ian Westwell
  • Start date Start date
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"))
 
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"))
 
Back
Top