Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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? |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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? |
#8
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get an IF statement to pull a date range?? | Excel Worksheet Functions | |||
Date Range within one cell | New Users to Excel | |||
Date Range and calculation | Excel Worksheet Functions | |||
Update cell based on date range | Excel Discussion (Misc queries) | |||
Count cells based on date range in another column | New Users to Excel |