count of records for a week

[vignesh-ram]

I need only a formula in the sheet2 tab to check count alone which will calculate the records for a particular week of 7 days. Say June 4 to June 10(if this is current week). If i add any issue in the sheet1 tab(june 9) then it should automatically increment the count in the sheet2 tab. If i delete an issue suppose june8 also, it should decrement the count by 1. Issues can be any many like for june 9 some 3 issues come or say june8 5 issues are solved. So appropriatly the increment and decrement should happen. Can someone help me with macro or formula?

Thanks,
V

 

[taurean]

Welcome to MrExcel.

What version of Excel do you use? There are many types of COUNT formula and other approaches which will work for you but you will have to put your information in clear example. You can post relevant data by copying table from Excel and pasting.

 

[vignesh-ram]

Welcome to MrExcel.

What version of Excel do you use? There are many types of COUNT formula and other approaches which will work for you but you will have to put your information in clear example. You can post relevant data by copying table from Excel and pasting.

Details tab

issue</SPAN>	opendate</SPAN>	owner</SPAN>
sa</SPAN>	6/4/2012</SPAN>	r</SPAN>
sd</SPAN>	6/5/2012</SPAN>	s</SPAN>
rest</SPAN>	6/10/2012</SPAN>	h</SPAN>
df</SPAN>	6/11/2012</SPAN>	f</SPAN>
s</SPAN>	6/12/2012</SPAN>	h</SPAN>
df </SPAN>	6/17/2012 </SPAN>	h</SPAN>
sdf</SPAN>	6/18/2012</SPAN>	d</SPAN>
s</SPAN>	6/19/2012</SPAN>	d</SPAN>
fds</SPAN>	6/20/2012</SPAN>	g</SPAN>
fsd</SPAN>	6/21/2012</SPAN>	d</SPAN>
f</SPAN>	6/22/2012</SPAN>	d</SPAN>
df</SPAN>	6/23/2012</SPAN>	dd</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL></COLGROUP>

Summary tab

	count</SPAN>
total tickets open</SPAN>	???

<TBODY>
</TBODY><COLGROUP><COL><COL></COLGROUP>

 

[Robert Mika]

Try this( if issue and owner does not matter)
Excel 2010

	A	B	C
1	issue	opendate	owner
2	sa	6/4/2012	r
3	sd	6/5/2012	s
4	rest	6/10/2012	h
5	df	6/11/2012	f
6	s	6/12/2012	h
7	df	6/17/2012	h
8	sdf	6/18/2012	d
9	s	6/19/2012	d
10	fds	6/20/2012	g
11	fsd	6/21/2012	d
12	f	6/22/2012	d
13	df	6/23/2012	dd

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Excel 2010

	A	B
1	4-Jun	10-Jun
2	Excel 03/07/10	Excel 07/10
3	3	3
4
5
6
7
8

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula A3 =SUMPRODUCT(--(Sheet1!$B$1:$B$1100>=A1),--(Sheet1!$B$1:$B$1100<=B1)) B3 =COUNTIFS(Sheet1!$B$1:$B$1100,">="&A1,Sheet1!$B$1:$B$1100,"<="&B1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[vignesh-ram]

Try this( if issue and owner does not matter)
Excel 2010

	A	B	C
1	issue	opendate	owner
2	sa	6/4/2012	r
3	sd	6/5/2012	s
4	rest	6/10/2012	h
5	df	6/11/2012	f
6	s	6/12/2012	h
7	df	6/17/2012	h
8	sdf	6/18/2012	d
9	s	6/19/2012	d
10	fds	6/20/2012	g
11	fsd	6/21/2012	d
12	f	6/22/2012	d
13	df	6/23/2012	dd

<TBODY>
</TBODY>

Sheet1​

Excel 2010

	A	B
1	4-Jun	10-Jun
2	Excel 03/07/10	Excel 07/10
3	3	3
4
5
6
7
8

<TBODY>
</TBODY>

Sheet2​

Worksheet Formulas Cell Formula A3 =SUMPRODUCT(--(Sheet1!$B$1:$B$1100>=A1),--(Sheet1!$B$1:$B$1100<=B1)) B3 =COUNTIFS(Sheet1!$B$1:$B$1100,">="&A1,Sheet1!$B$1:$B$1100,"<="&B1) <TBODY> </TBODY>

<TBODY>
</TBODY>

Thanks for the help. Instead of A1,B1 values i used the today()-7 function to get the result. However can you tell me how to get make the issues to be displayed by clicking on the count value.

 

[Robert Mika]

Thanks for the help. Instead of A1,B1 values i used the today()-7 function to get the result. However can you tell me how to get make the issues to be displayed by clicking on the count value.

Can you elaborate the last requst?