# count of records for a week

#### vignesh-ram

##### New Member
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.

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

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

Summary tab
 count total tickets open ???

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

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

</tbody>
Sheet1

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

</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)

</tbody>

<tbody>
</tbody>

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.

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?

Replies
1
Views
230
Replies
3
Views
333
Replies
6
Views
372
Replies
1
Views
319
Replies
6
Views
422

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.

### Which adblocker are you using?

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

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