count of records for a week

vignesh-ram

New Member
Joined
Jun 23, 2012
Messages
7
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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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.
 
Upvote 0
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>
 
Upvote 0
Try this( if issue and owner does not matter)
Excel 2010
ABC
1issue opendate owner
2sa 6/4/2012r
3sd 6/5/2012s
4rest 6/10/2012h
5df 6/11/2012f
6s 6/12/2012h
7df 6/17/2012h
8sdf 6/18/2012d
9s 6/19/2012d
10fds 6/20/2012g
11fsd 6/21/2012d
12f 6/22/2012d
13df 6/23/2012dd

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



Excel 2010
AB
14-Jun10-Jun
2Excel 03/07/10Excel 07/10
333
4
5
6
7
8

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

Worksheet Formulas
CellFormula
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>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,099
Messages
6,105,955
Members
447,986
Latest member
dicklim39

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top