Sumproduct based on date entered in separate field

[Mercdoodle]

I'm a rusty Excel user, and designed a sheet to report on monthly values
"items closed" using Sumproduct as indicated below.

Here are my fields on the report tab:
Column B: Date checked (data entry cell)

Column C: Number Submitted
=SUMPRODUCT(--(MONTH('Data Entry'!$B$2:$B$2000)=1),--(YEAR('Data
Entry'!$B$2:$B$2000)=2009))

Column D: Number Completed
=SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data
Entry'!$B$2:$B$2000>=DATE(2009,1,1)),--('Data
Entry'!$B$2:$B$2000<=DATE(2009,1,31)))

I have now found that they want it reported "as of the date checked" which
is sometime between the 25th and last date of the month, depending. I have a
data entry field for the date checked, but I must have to change the way I'm
pulling the date. It doesn't like just a plain old cell reference. Help?

 

[T. Valko]

Not sure what you're asking for.

If you enter an "as of the date checked" how should this be incorporated
into your existing formulas?

 

[Mercdoodle]

Yes, that's it. I designed the sheet to calculate based on the calendar
month, but now they want it monthly "as of the date checked", where the user
enters a date between the 25th and end of month. Column A is the month (I
didn't list that one below, nor the one that calculates the % complete, which
is Column E)

 

[T. Valko]

Ok, to use that date in your current formulas...

A1 = a user entered date ("as of the date checked")

Column C: Number Submitted

=SUMPRODUCT(--(MONTH('Data Entry'!$B$2:$B$2000)=MONTH(A1)),--(YEAR('Data
Entry'!$B$2:$B$2000)=YEAR(A1)))

Column D: Number Completed

=SUMPRODUCT(--('Data Entry'!$F$2:$F$2000="Closed"),--('Data
Entry'!$B$2:$B$2000>=A1),--('Data
Entry'!$B$2:$B$2000<=DATE(YEAR(A1),MONTH(A1)+1,0)))