How to quickly count appearance in every 15 minutes in Excel?
Here is a list of time about the calling records in a sheet. In this case, I want to count the times of the calling records in every 15 minutes as below screenshot shown, do you have any good idea?
Recommended Productivity Tools for Excel/Office
To count the times of records in every 15 minutes, you need to use some formulas first and then create a PivotTable.
1. Firstly, you need to calculate the total number of quarters in a day. You can create a range of cells as following screenshot shown, and type formula =B2 *C2 0r directly type =24*4 in cell D2, type =1/D2 or = 1/96 in cell E2.
2. Then select a blank cell next to the data of time, type this formula =INT(A4/$E$2)*$E$2, E2 is the cell that you use formula =1/96 in above step. See screenshot:
3. Drag Autofill handle down to apply this formula to the other cells. See screenshot:
4. In the next column, column C, type 1 into the cells beside the formulas. See screenshot:
5. Select the range including headers, and click Insert > PivotTable. See screenshot:
6. In the Create PivotTable dialog, choose to place the Pivot Table in a new sheet or existing sheet. See screenshot:
7. Click OK, and then in the PivotTable Fields pane, add a When column (the formulas column) to ROWS list and add Count column (the number 1 column) to VALUES list. See screenshot:
Now the times of callings have been counted out in every 15 minutes.
|In some cases, you may have a range of calues with multiple colors, and what you want is to count/sum values based on same color, how can you quickly calculate?
With Kutools for Excel's Count by Color, you can quickly do many calculations by color, and also can generate a report of the calculated result.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
300 New Features for Excel, Make Excel Much Easy and Powerful:
- Merge Cell/Rows/Columns without Losing Data.
- Combine and Consolidate Multiple Sheets and Workbooks.
- Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
- Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
- More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...