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
for example , the user select from 31-03-2001 to 31-12-2012 , then output the report which show the total no. of success and no. of failure 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

thanks

On Sunday, October 27, 2013 11:15:24 PM UTC-7, amy chan wrote:
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.

thanks reply ,

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

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.

thanks reply ,

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

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/tr...006118141.aspx

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