export data by selecting date range

[amy chan]

I would like to create a excel file to let the user to input th
following column.

Date No. of success No. of failure
==== ============ ==============
" " "
" " "
" " "

Could advise if I would like to have a function that can allow the use
to select the date range and its total no. of success and no. o
failure
for example , the user select from 31-03-2001 to 31-12-2012 , the
output the report which show the total no. of success and no. of failur
in this date range , is there existing tamplate that I can use ? if no
what is the simplier way to do it ?

if the data is very large , I want to create a access db to store it
could advise how to do it ?

Please advise

thank

 

[zvkmpw]

I would like to create a excel file to let the user to input the
following column.

Date No. of success No. of failure
==== ============ ==============
" " "
" " "
" " "
Could advise if I would like to have a function that can allow the user
to select the date range and its total no. of success and no. of
failure

With the data above in columns A, B, C, from rows 2 through 1000, allow the user to put the start of the date range in E1 and the end of the date range in E2.

Then for the total no. of success try:
=SUMIF(A2:A1000,"<="&E2,B2:B1000)-SUMIF(A2:A1000,"<"&E1,B2:B1000)

And for failures:
=SUMIF(A2:A1000,"<="&E2,C2:C1000)-SUMIF(A2:A1000,"<"&E1,C2:C1000)

Modify as needed. Hope this helps getting started.

 

[amy chan]

With the data above in columns A, B, C, from rows 2 through 1000, allo
the user to put the start of the date range in E1 and the end of th
date range in E2.

Then for the total no. of success try:
=SUMIF(A2:A1000,"<="&E2,B2:B1000)-SUMIF(A2:A1000,"<"&E1,B2:B1000)

And for failures:
=SUMIF(A2:A1000,"<="&E2,C2:C1000)-SUMIF(A2:A1000,"<"&E1,C2:C1000)

Modify as needed. Hope this helps getting started.

thanks reply ,

Question 1 ) &E2,C2:C1000 is date field ?
Question 2 ) if I wuld like to have a database ( eg. access ) to stor
the large data , how to make it

 

[zvkmpw]

Question 1 ) &E2,C2:C1000 is date field ?

Here's an explanation of the arguments of SUMIF here.

SUMIF(
A2:A1000, -- the dates to be compared with the upper/lower limit
"<="&E2, -- the test: "less than or equal to the upper limit"
C2:C1000 -- the counts summed when the test is met
)

The "&" in the second argument is string concatenation. So if the upper limit in E2 is 31-12-2012 then the test is (in effect)
<= 31-12-2012

Combining the two SUMIFS, the formula says
Start with the sum for dates <= the upper limit.
Then subtract off the sum for dates < the lower limit.
This leaves the sum for dates in between.

Question 2 ) if I wuld like to have a database ( eg. access ) to store
the large data , how to make it ?

Maybe somebody else can respond to this part.

 

[amy chan]

thanks reply ,

could advise if I want to use access db to do it , how to make it
thank

 

[zvkmpw]

could advise if I want to use access db to do it , how to make it ?

I haven't used Access. Surely others reading this have used it.

Microsoft has a tutorial at
http://office.microsoft.com/en-us/training/get-to-know-access-RZ006118141.aspx

There are Access-specific newsgroups, for example:
microsoft.public.access.gettingstarted