Computing a date range

[Guest]

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?

 

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

 

[Guest]

Bob,

That did it.

Thanks,

Roy

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

 

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

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

 

[Guest]

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.

 

[Guest]

Ron,

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

 

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

 

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

--ron