counting how many times something happens in a week

[lukesdad]

i have a database that contails a row of days then one with names

i need to count how many times a name shows up per week

the original database is set up like

date name

3/14/05 tom
3/15/05 frank


so basically how do i group the individual days into week format then count
the times a name show up per week

 

[Sean Timmons]

It woudl seem easiest to use a Pivot table here.

Highlight your data, go to Data - Pivot Table

Drop your date and names into Row Fields. Right-click on the list of dates
and seelct Group and Show Detail, then Group...

You should be able to group by weeks here. You can change your start and
end dates. Any data prior to or after these dates will fall into a <start and

end column.

Drop your names into Data Fields as well.

This will give count of each name per week.

 

[Sean Timmons]

It woudl seem easiest to use a Pivot table here.

Highlight your data, go to Data - Pivot Table

Drop your date and names into Row Fields. Right-click on the list of dates
and seelct Group and Show Detail, then Group...

You should be able to group by weeks here. You can change your start and
end dates. Any data prior to or after these dates will fall into a <start and

end column.

Drop your names into Data Fields as well.

This will give count of each name per week.

 

[Jim Thomlinson]

Here is how I would do that... Add an extra column to the right of the data
and call it week. Add the formula =WeekNum(A2) assuming that your dates are
in column A with a header in row 1.

Select Data -> Pivot Table -> follow the wizard.
Place the persons name and Week in the left column and names to the data
section to get a count of the names. For this source data

Date Person Week
1-Jan Tom 1
2-Jan Dave 1
3-Jan Tom 1
4-Jan Dave 2
5-Jan Tom 2
6-Jan Dave 2
7-Jan Tom 2
8-Jan Dave 2
9-Jan Tom 2
10-Jan Dave 2
11-Jan Tom 3
12-Jan Dave 3
13-Jan Tom 3
14-Jan Dave 3
15-Jan Tom 3
16-Jan Dave 3
17-Jan Tom 3
18-Jan Dave 4
19-Jan Tom 4

I got...
Count of Person
Person Week Total
Dave 1 1
2 4
3 3
4 1
Dave Total 9
Tom 1 2
2 3
3 4
4 1
Tom Total 10
Grand Total 19

 

[Jim Thomlinson]

Here is how I would do that... Add an extra column to the right of the data
and call it week. Add the formula =WeekNum(A2) assuming that your dates are
in column A with a header in row 1.

Select Data -> Pivot Table -> follow the wizard.
Place the persons name and Week in the left column and names to the data
section to get a count of the names. For this source data

Date Person Week
1-Jan Tom 1
2-Jan Dave 1
3-Jan Tom 1
4-Jan Dave 2
5-Jan Tom 2
6-Jan Dave 2
7-Jan Tom 2
8-Jan Dave 2
9-Jan Tom 2
10-Jan Dave 2
11-Jan Tom 3
12-Jan Dave 3
13-Jan Tom 3
14-Jan Dave 3
15-Jan Tom 3
16-Jan Dave 3
17-Jan Tom 3
18-Jan Dave 4
19-Jan Tom 4

I got...
Count of Person
Person Week Total
Dave 1 1
2 4
3 3
4 1
Dave Total 9
Tom 1 2
2 3
3 4
4 1
Tom Total 10
Grand Total 19