Date SUM/COUNT

[hillari158]

From within a column in a separate worksheet I am trying to determine how to sum or count the number of dates listed within that column that fall within a certain date range. For instance, April 1 through April 30, 2009; if there are 10 dates in that column that fall within that date range I want the formula to return the sum of 10.

Thanks.

[DonkeyOte]

A few options in truth, one being

=SUMPRODUCT(--(Sheet2!A1:A100-DAY(Sheet2!A1:A100)+1=DATE(2009,4,1)))

useful if doing monthly analysis... for more specific from / to dates, eg 1st April to 20th then:

=SUMPRODUCT(--(Sheet2!A1:A100>=DATE(2009,4,1)),--(Sheet2!A1:A100<=DATE(2009,4,20)))

[hillari158]

For some reason, it returns the error #NUM!. I haven't been able to determine why?

[DonkeyOte]

I suspect you are trying to use entire column references - ie A:A ... you can't do that preXL07 and nor should you in a Sumproduct formulae as performance will be affected... keep ranges to a minimum.

[hillari158]

Yes, that was it. Thanks so much!

[hillari158]

I need to add one more part to this formula... I need to compare two date range columns as well as another column. The last column returns a 1 if the task started and a 0 if it did not. This will allow me to only include the date in the sum if all requirements have been met. I want to determine whether a task started early, started on time, started late, etc. This is what I came up with but I don't know how to include it.


With this example I am trying to determine an early start...
--('Input Data'!K2:K1760,'Input Data'!G2:G1760='Input Data'!H2:H1760,'Input Data'!H2:1760>0)

[hillari158]

Got it!

=SUMPRODUCT(--('Input Data'!H2:H1760>=DATE(2009,4,20)),--('Input Data'!H2:H1760<=DATE(2009,4,30)),--('Input Data'!G2:G1760='Input Data'!H2:H1760))