# Excel 2007 subtotal

I have 2 columns in a spreadsheet Date and Hour and I want to count how many of each hour for each date.  When I use subtotal it leaves the Date off on the totals.
Date                    Hour
20091220      00
20091220      00
20091220      00
20091220      00
20091220      00
20091220      00
20091220      00
20091220      00
20091220      01
20091220      01
20091220      01
20091220      01
20091220      01
20091220      01
20091220      01
20091220      01
20091220      01
20091220      01

How do I get it to show 20091220 00 8
20091220 01 10

Subtotal just shows me the count total for the hour but not the date.
LVL 5
###### Who is Participating?

Commented:
You should do a pivot table instead of subtotal, with date and hour as row fields, and hour as data field

You can't do an automatic subtotal on two fields, but you could do it manually if you really want a subtotal.

Thomas
0

Commented:
Depending on how you want this to work you could add a third column with this formula:
=A2&" "&TEXT(B2,"0").  This concatenates the values in columns A & B and converts the hour column value to text resulting in a new string value "20091220 0" in the third column, then run the Subtotal function with "At Each Change In" of the third column using the "Count" function.
20091220 0 Count      8
20091220 1 Count      10

Convert.xls
0

Author Commented:
Great Ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.