Computing a date range

R

roy.okinawa

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?
 
B

Bob Phillips

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

roy.okinawa

Bob,

That did it.

Thanks,

Roy
Bob Phillips said:
=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)
Click to expand...
 
R

Ron Rosenfeld

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?
Click to expand...

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
 
R

roy.okinawa

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.
 
R

roy.okinawa

Ron,

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

Bob Phillips

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

Ron Rosenfeld

You can use the same principle using COUNTIF:

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



Ron,

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

--ron
 
Top