Computing a date range
 

I want to have two cells where I enter the start date and end date to
sum/count totals between that date range. I can get it to work if I just
enter one date. See below.
=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),--(ISNUMBER(SEARCH(AS61,N45:N1014))))

What is the rest of the formula to compute the end date as well?

Computing a date range
 

=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),(--(MONTH(V45:V1014)<=MONTH(AV
52)),--(ISNUMBER(SEARCH(AS61,N45:N1014))))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"roy.okinawa" wrote in message
...
I want to have two cells where I enter the start date and end date to
sum/count totals between that date range. I can get it to work if I just
enter one date. See below.

=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),--(ISNUMBER(SEARCH(AS61,N45:N10
14))))

What is the rest of the formula to compute the end date as well?

Computing a date range
 

Bob,

That did it.

Thanks,

Roy
"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),(--(MONTH(V45:V1014)<=MONTH(AV
52)),--(ISNUMBER(SEARCH(AS61,N45:N1014))))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"roy.okinawa" wrote in message
...
I want to have two cells where I enter the start date and end date to
sum/count totals between that date range. I can get it to work if I just
enter one date. See below.

=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),--(ISNUMBER(SEARCH(AS61,N45:N10
14))))

What is the rest of the formula to compute the end date as well?

Computing a date range
 

On Mon, 14 Nov 2005 15:07:03 -0800, "roy.okinawa"
wrote:

I want to have two cells where I enter the start date and end date to
sum/count totals between that date range. I can get it to work if I just
enter one date. See below.
=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),--(ISNUMBER(SEARCH(AS61,N45:N1014))))

What is the rest of the formula to compute the end date as well?

Your formula is only looking at the month. If there are multiple years, it
will detect all the data in all the January's for example.

If you want to SUM a series of entries between two dates, that are entered in
AU52 and AV52 then:

=SUMIF(V45:V1014,"="&AU52) - SUMIF(V45:v1014,""&AV52)


--ron

Computing a date range
 

Bob,

Now that I have enter the formula throughout the spreadsheet, some are
working and some are coming back with a #VALUE!

What would cause this? There is nothing different in these cells, than
there is in the ones that are working. It is dates and text.

"Bob Phillips" wrote:

=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),(--(MONTH(V45:V1014)<=MONTH(AV
52)),--(ISNUMBER(SEARCH(AS61,N45:N1014))))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"roy.okinawa" wrote in message
...
I want to have two cells where I enter the start date and end date to
sum/count totals between that date range. I can get it to work if I just
enter one date. See below.

=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),--(ISNUMBER(SEARCH(AS61,N45:N10
14))))

What is the rest of the formula to compute the end date as well?

Computing a date range
 

Ron,

This formula is adding the dates. I need just to sum/count the total of
each date contained between the "From" "To" dates.

"Ron Rosenfeld" wrote:

On Mon, 14 Nov 2005 15:07:03 -0800, "roy.okinawa"
wrote:

I want to have two cells where I enter the start date and end date to
sum/count totals between that date range. I can get it to work if I just
enter one date. See below.
=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),--(ISNUMBER(SEARCH(AS61,N45:N1014))))

What is the rest of the formula to compute the end date as well?

Your formula is only looking at the month. If there are multiple years, it
will detect all the data in all the January's for example.

If you want to SUM a series of entries between two dates, that are entered in
AU52 and AV52 then:

=SUMIF(V45:V1014,"="&AU52) - SUMIF(V45:v1014,""&AV52)


--ron

Computing a date range
 

Probably not using absolute references, which is needed if copying down

=SUMPRODUCT(--(MONTH($V$45:$V$1014)=MONTH(AU52)),(--(MONTH($V$45:$V$1014)<=
MONTH(AV52)),--(ISNUMBER(SEARCH(AS61,$N$45:$N$1014))))

I have assumed all the rest change with the row?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"roy.okinawa" wrote in message
...
Bob,

Now that I have enter the formula throughout the spreadsheet, some are
working and some are coming back with a #VALUE!

What would cause this? There is nothing different in these cells, than
there is in the ones that are working. It is dates and text.

"Bob Phillips" wrote:


=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),(--(MONTH(V45:V1014)<=MONTH(AV
52)),--(ISNUMBER(SEARCH(AS61,N45:N1014))))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"roy.okinawa" wrote in message
...
I want to have two cells where I enter the start date and end date to
sum/count totals between that date range. I can get it to work if I
just
enter one date. See below.


=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),--(ISNUMBER(SEARCH(AS61,N45:N10
14))))

What is the rest of the formula to compute the end date as well?

Computing a date range
 

You can use the same principle using COUNTIF:

=COUNTIF(V45:V1014,"="&AU52) - COUNTIF(V45:v1014,""&AV52)



On Mon, 14 Nov 2005 16:37:07 -0800, "roy.okinawa"
wrote:

Ron,

This formula is adding the dates. I need just to sum/count the total of
each date contained between the "From" "To" dates.

"Ron Rosenfeld" wrote:

On Mon, 14 Nov 2005 15:07:03 -0800, "roy.okinawa"
wrote:

I want to have two cells where I enter the start date and end date to
sum/count totals between that date range. I can get it to work if I just
enter one date. See below.
=SUMPRODUCT(--(MONTH(V45:V1014)=MONTH(AU52)),--(ISNUMBER(SEARCH(AS61,N45:N1014))))

What is the rest of the formula to compute the end date as well?

Your formula is only looking at the month. If there are multiple years, it
will detect all the data in all the January's for example.

If you want to SUM a series of entries between two dates, that are entered in
AU52 and AV52 then:

=SUMIF(V45:V1014,"="&AU52) - SUMIF(V45:v1014,""&AV52)


--ron


--ron