How to count records in one column, for each value in other column? 2

[JonAtHQ]

Here's my challeng that I'm hoping you can help with. I have a report (see attached) showing the dates our students completed a class (in column A), and the region where they are from (in Column B)

Column A = Date
Column B = Region

I want a summary to show how many from each region took the class each month. Sort of like this:

North
Jan 3
Feb 5
Mar 4
Total 12

South
Jan 2
Feb 6
Mar 3
Total 11

OR it could be formatted as such:

Jan Feb Mar Total
North 3 5 4 12
South 2 6 3 11

I was able to create a pivot table to show how many students from the region took the class. But I am not able to then take the total for a region and separate it out by months.

Thanks in advance.

 

[petrosky]

Hi Jon,

The easiest way is to add another column with the number 1 beside each and sum on this column in the pivot table.

Hope this helps.

petrosky

Remember- It's nice to be important,
but it's important to be nice

 

[judgeh59]

It's a bit confusing on how you have your data set up...if it is set like this

Col A Col b
Jan North
Jan North
Feb North
Mar South
...
then a pivot table with Column labels being Date(col A) and row Labels being Region(Col b) and values being Count of Date(Col A) that break everything out. Every here will tell you setting up your data in the beginning is the most important step of your process...

HTH

Ernest

Be Alert, America needs more lerts

 

[SkipVought]

This is a job for a PivotTable.

GROUP the date field on MONTH & YEAR.

COUNT Of Region.

Should be a 30 second task!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[JonAtHQ]

Thanks Petrosky, Ernest and Skip. You forced me to learn Pivot Tables! But it took me about 90 seconds, not 30. Guess I'll take it!

By the way ... at one point, the Field Table wouldn't appear. And, yes, the Field List butto was depressed. Googling the issue told me that I needed to start up Excel in Safe Mode (launching it while pressing Ctrl). That did the trick.

Once again, thanks!!!