subtotal dates/times
Hello again, Experts, I need your help.
See attached example. Column A contains date/time entries (1/6/2014 7:38:52 PM). I want to sub-total them by how many entries there are for each hour segment. For example:
1/2/2014 5pm = 3
1/3/2014 5pm = 4
1/6/2014 5pm= 3
1/6/2014 6pm = 1
1/6/2014 7pm = 1
Thanks,
Gary (frigid still in Cincinnati)
N-Kansas-City-60-day-Study-EE-Su.xlsx
See attached example. Column A contains date/time entries (1/6/2014 7:38:52 PM). I want to sub-total them by how many entries there are for each hour segment. For example:
1/2/2014 5pm = 3
1/3/2014 5pm = 4
1/6/2014 5pm= 3
1/6/2014 6pm = 1
1/6/2014 7pm = 1
Thanks,
Gary (frigid still in Cincinnati)
N-Kansas-City-60-day-Study-EE-Su.xlsx
You can also summarize with a Pivot Table.
Select the column.. go to Insert Pivot Table, drag the item to the Row Label area, then drag again to the Summation area.
You can then right click the row label column and select Group, then select Dates and Hours (you can adjust Start/End times)
Select the column.. go to Insert Pivot Table, drag the item to the Row Label area, then drag again to the Summation area.
You can then right click the row label column and select Group, then select Dates and Hours (you can adjust Start/End times)
ASKER
where is the "Summation area"?
ASKER
=COUNTIFS(A:A,">="&B2,A:A,
Have you tested this formula in Col B of my sample file?
I get a circular reference warning.
Have you tested this formula in Col B of my sample file?
I get a circular reference warning.
Its the section to the right of the row label area (Sigma Values)
The formula goes in column C. It references your column B inputs against the column A data.
again, the summation area is the area of (S Values)
... can't enter Sigma symbol
... can't enter Sigma symbol
ASKER
Ok, so I am a newbie to Pivot Tables...
I got the Summation area drop...
Col B now shows "Count of Start Date/Time"
[ right click the row label column and select Group, then select Dates and Hours ]
When I "right click col A" I don't see a "select group" option.
What to do?
I got the Summation area drop...
Col B now shows "Count of Start Date/Time"
[ right click the row label column and select Group, then select Dates and Hours ]
When I "right click col A" I don't see a "select group" option.
What to do?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, that was fun and easy. Thank you. You guys are terrific!
A special thanks for the detailed description of steps.
Gary
A special thanks for the detailed description of steps.
Gary
=COUNTIFS(A:A,">="&B2,A:A,
copied down.