?
Solved

Excel 2007 subtotal

Posted on 2009-12-29
3
Medium Priority
?
511 Views
Last Modified: 2012-06-27
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
Comment
Question by:MikeLogsdon
3 Comments
 
LVL 39

Accepted Solution

by:
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

by:normajm400
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

by:MikeLogsdon
ID: 31670948
Great Ty
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question