counting how many times something happens in a week

  • Thread starter Thread starter lukesdad
  • Start date Start date
L

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
 
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.
Click to expand...

Drop your names into Data Fields as well.

This will give count of each name per week.
 
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.
Click to expand...

Drop your names into Data Fields as well.

This will give count of each name per week.
 
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
 
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
 
Back
Top