Sumproduct based on date entered in separate field
 

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?

Sumproduct based on date entered in separate field
 

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?


--
Biff
Microsoft Excel MVP


"Mercdoodle" wrote in message
...
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?

Sumproduct based on date entered in separate field
 

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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Mercdoodle" wrote in message
...
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?

Sumproduct based on date entered in separate field
 

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


--
Biff
Microsoft Excel MVP


"Mercdoodle" wrote in message
...
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" wrote:

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?


--
Biff
Microsoft Excel MVP


"Mercdoodle" wrote in message
...
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?