SUMPRODUCT with date functions

  • Thread starter Thread starter MCA
  • Start date Start date
M

MCA

This is a formula that is close, but does not work ...
SUMPRODUCT((Sheet1!A2:A5555=(BETWEEN "09/01/2003"
AND "09/31/2003"))*(Sheet1!I1:I5555=Totals!C1)) . What I
would like is to check Sheet1 for a date range in column A
AND check Sheet1 for a value in column I and if both
criteria match, then count the number of instances that it
matches. I think my date range is set up wrong. Please
help.
 
One way

=SUMPRODUCT((Sheet1!A2:A5555>=DATE(2003,09,01))*(Sheet1!A2:A5555<=DATE(2003,
09,30)),
Sheet1!I2:I5555=Totals!C1)

note that September has only 30 days and that the ranges have to have the
same dimension (I1 in your example
has to be I2 if the dates start in A2)
 
MCA,

Either of these will give a count of the dates within a range of dates.
Adjust your cell references as needed. Also, you can change the
inequalities to <= or >= if you wish to include the start/end dates.

=COUNTIF(A1:A10,"<"&DATE(2003,9,23))-COUNTIF(A1:A10,"<"&DATE(2003,9,1))


=SUMPRODUCT((A1:A10<DATE(2003,9,23)*(A1:A10>DATE(2003,9,1)*(A1:A10))


PC
 
-----Original Message-----
This is a formula that is close, but does not work ...
SUMPRODUCT((Sheet1!A2:A5555=(BETWEEN "09/01/2003"
AND "09/31/2003"))*(Sheet1!I1:I5555=Totals!C1)) . What I
would like is to check Sheet1 for a date range in column A
AND check Sheet1 for a value in column I and if both
criteria match, then count the number of instances that it
matches. I think my date range is set up wrong. Please
help.

.
Click to expand...
 
Back
Top