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"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

countifs Function not giving desired result with date criteria 2
Sumproduct Help! 3
Sumproduct in matrix means 3
Default IF Date Is between 2 Dates then output additional column 1
Count the remaining weekdays in a data range excluding holidays and start/end dates 5
formula for multiple criteria inc. date range 4
How to get largest date from a Vlookup 1
Counting cells with SUMPRODUCT and conditionals...? 1

Top