How can I count data by group in excel
I have data of program usage in excel file, the data has date and time. I want to summarize this information that in each day each hour, how many user use this application. How to do that using excel formular?
sample data:
Jun 01, 09:03AM
Jun 01, 10:00AM
Jun 01, 10:50AM
Jun 01, 11:00AM
Jun 01, 11:26AM
Jun 02, 01:00PM
Jun 02, 03:00PM
Jun 02, 04:04PM
Jun 02, 08:00PM
Jun 02, 11:00AM
Jun 02, 12:00PM
Jun 01, 10:50AM
Jun 01, 11:00AM
Jun 02, 11:00AM
Jun 02, 12:00PM
Jun 02, 01:00PM
Jun 03, 08:00PM
Jun 03, 09:00PM
Answer
Create a pivot table.
Create a new column call 'Day' next to your data. Put a formula in the column to extract the day.
=DAY(A2)
Create a pivot table based on the two columns of data.
- Row fields = Column 1 dates
- Column fields - Column 2 day
- Data items - Count of Column 1 dates
Group the Row fields by hours.
If you want the full day (i.e. Jun 1) then instead of creating a day column, create a copy of your data in a second column. Use the two columns in the pivot table and group the new column by day.
To do the same purely by formulas in Excel 2007.
Extend the data by adding two columns.
Call the first column 'Day'. Put a formula in the column to extract the day.
=DAY(A2)
Call the second column 'Hour'. Put a formula in the column to extract the hour.
=HOUR(A2)
Create a table layout with columns headed 1 to 31 for days and rows headed 0 to 23 for hours.
Insert the following formula (adjust cell references as required)
=COUNTIFS($B$1:$B$19,F$1,$C$1:$C$19,$E3)
Copy the formula to fill the table.
The COUNTIFS function is new to Excel 2007. It works in the same way as the COUNTIF function, but allows multiple ranges and criteria.