Solved

# Excel 2007 subtotal

Posted on 2009-12-29
Medium Priority
511 Views
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.
0
Question by:MikeLogsdon

LVL 39

Accepted Solution

nutsch earned 2000 total points
ID: 26141246
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

Expert Comment

ID: 26141508
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

LVL 5

Author Closing Comment

ID: 31670948
Great Ty
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
###### Suggested Courses
Course of the Month14 days, 4 hours left to enroll