Excel Sum Hourly Data to Daily: Easy Methods

To sum hourly data into daily totals in Excel, use a PivotTable with the hourly timestamps grouped by day, or use the SUMPRODUCT function with a date match. This article explains both methods step by step, along with how to handle common issues like missing hours and time-only data.

Formula

The calculator above uses the simple formula:

Total = Time (hours) × Rate (units per hour)

• Time (t) – the number of hours you want to sum (e.g., 24 for a full day).
• Rate (r) – the average rate per hour (e.g., 10 units per hour).
• Total – the resulting daily total.

This is just a quick helper. The real work in Excel is grouping your hourly rows into daily sums, which the methods below cover.

Worked Example

Suppose you have 24 hours of data with an average rate of 10 units per hour. Using the default values:

1. Time = 24 hours
2. Rate = 10 units/hour
3. Total = 24 × 10 = 240 units

The calculator shows 240.00 units, matching the formula.

Method 1: PivotTable Grouping (Easiest)

PivotTables are the fastest way to sum hourly data by day, especially for large datasets. Here’s how:

1. Select any cell in your data range (must have headers like “Date/Time� and “Value�).
2. Go to Insert → PivotTable → choose where to place it.
3. In the PivotTable Fields pane, drag the Date/Time field to the Rows area.
4. Drag the Value field to the Values area (it should default to Sum, but you can change it to Average, Count, etc.).
5. Right-click any date in the PivotTable, select Group, then choose Days (and optionally uncheck “Months� if it’s auto-selected). Click OK.

The PivotTable now shows one row per day with the sum of all hourly values for that day. If your data spans multiple years, group by Days and Years to avoid mixing same dates from different years.

Method 2: SUMPRODUCT Formula (No PivotTable)

If you prefer a formula-based approach (e.g., for a dashboard), use SUMPRODUCT to sum values where the date matches a cell. This works when your timestamps include both date and time.

1. Create a list of unique dates in column D (e.g., D2 = 2025-01-01).
2. In E2, enter: =SUMPRODUCT((INT($A$2:$A$1000)=D2)*$B$2:$B$1000)
3. Drag down for each date.

Here, INT() strips the time from the timestamp, leaving the date. The formula multiplies a TRUE/FALSE array (converted to 1/0) by the values, summing only those for the matching date. Adjust the range to fit your data.

Method 3: Using a Helper Column with SUMIF

Another robust approach is to add a helper column that extracts the date, then use SUMIF. This is easier to audit and works in older Excel versions.

1. In a new column (e.g., C), enter =INT(A2) for the first data row and copy down. This gives the date (as a number).
2. Format column C as a date if needed.
3. Create a list of unique dates in column E.
4. In F2, enter: =SUMIF($C$2:$C$1000, E2, $B$2:$B$1000)
5. Copy down for all dates.

This method is transparent and avoids the complexity of array formulas. It also works if your timestamps are text that Excel recognizes as dates.

Common Pitfalls and How to Avoid Them

• Time-only data – If your timestamps are times (e.g., 08:00) without a date, you cannot group by day because there is no date info. You must combine with a date column first.
• Missing hours – If some hours are missing, a simple sum still gives the total for the hours you have. If you need a daily average per hour, divide the sum by the number of non-empty cells (use COUNT).
• Date format issues – If Excel doesn’t recognize your timestamps as dates, the INT() function will return a #VALUE! error. Convert text to dates using Data → Text to Columns (choose Date) or use DATEVALUE.
• Blank cells – SUMPRODUCT and SUMIF ignore blank cells, but if a cell contains text, it may cause errors. Clean your data first.
• PivotTable grouping error – If you get “Cannot group that selection,â€� it usually means the source data contains text or blank cells in the date column. Remove blanks and ensure all entries are real dates.

When to Use Each Method

• PivotTable – Best for quick analysis, large datasets, and when you need to switch between sums, averages, or counts without rewriting formulas.
• SUMPRODUCT – Ideal when you want a formula that updates automatically as you add data (if you use a dynamic range or table).
• Helper column + SUMIF – Great for transparency, sharing with others, and when you need to filter or sort by date separately.

All methods produce the same result if your data is clean. Choose based on your comfort and the need for automation.

Frequently Asked Questions

How do I sum hourly data by day in Excel without a PivotTable?

Use the SUMPRODUCT formula with INT() to extract the date, or add a helper column with =INT(A2) and then use SUMIF. Both avoid PivotTables and update automatically if you reference the correct ranges.

Why does my PivotTable show hours instead of days?

Right-click any date in the PivotTable, choose Group, and select “Days� (and “Years� if needed). If the Group option is greyed out, your data may contain text or blank cells – fix those first.

Can I sum hourly data by day if my timestamps are in separate columns (date and time)?

Yes. Combine them into one column using =A2+B2 (if date is in A and time in B), then use any of the methods above. Or, if they are already separate, you can group by the date column directly in a PivotTable.

How do I handle missing hours when summing to daily totals?

If you need the total for the hours present, a simple sum works. If you need an average per hour, divide the sum by the count of non-empty cells. For a complete day, you may need to fill missing hours with zeros or interpolate.

What if my hourly data includes multiple years? Will grouping by day mix them?

Yes, if you only group by “Days� in a PivotTable, dates from different years will be combined. Group by both “Years� and “Days� to keep each year separate. In formulas, use a criteria that includes the year, e.g., =SUMPRODUCT((YEAR(A:A)=2025)*(INT(A:A)=D2)*B:B).

Related

• Mac Lock Screen: Best Ways to Secure Your Computer
• Business Laptop 5 Essentials for Your Team
• Xbox App Slow Downloads? Speed Up in Windows 11

in t | Time (hours) | h | 24 | 0
in r | Rate (units/h) | units/h | 10 | 0
out total | Total units | units | t * r | 2