Function Problems

How to Use SUMPRODUCT to Add Skill Ratings by Hour in Excel

Khadija Khan Sep 28, 2026 868 views

Question details

The user needs to build an hourly team-skill heat map by summing the skill ratings of people scheduled during specific hourly time buckets using a copyable formula.

How to Use SUMPRODUCT to Add Skill Ratings by Hour in Excel

Product: Excel
Device & OS: not provided
Scenario: Creating an employee scheduling heat map to evaluate the total available skill rating during different hours of the day.
Observed behavior: A specific SUMPRODUCT formula is required to correctly extract shift times, compare them against the hour buckets, and sum the corresponding numerical ratings across an hourly grid.

Before you start

Ensure your scheduling data consistently formats start and end times, and verify that the column headers in your rota table exactly match the labels in your ratings table.

Solution 1Recommended

Use SUMPRODUCT and Text Functions for Hourly Time Buckets

Combine SUMPRODUCT with LEFT, RIGHT, VALUE, and IFERROR to extract schedule times, compare them with hourly buckets, and calculate total skill ratings.

This formula extracts the start and end times formatted as text (e.g., '09:00-17:00') from the schedule cells, converts them to values, and checks if they overlap with the specified hourly bucket. It then multiplies the valid matches by the corresponding skill rating.

Set up the ratings row

Place the specific numerical ratings corresponding to each employee or role in a fixed range, such as E8:K8, above or below your schedule table.

Input the SUMPRODUCT formula

In the first hourly bucket cell (assuming N2 contains the hour reference), enter the formula: =SUMPRODUCT($E$8:$K$8*(IFERROR(VALUE(LEFT($E3:$K3,5))<N$2,0))*(IFERROR(VALUE(RIGHT($E3:$K3,5))>N$2,0)))

Copy the formula across the grid

Select the cell containing the formula and drag the fill handle across the columns and rows to populate your entire hourly team-skill heat map.

Use SUMPRODUCT and Text Functions for Hourly Time Buckets

Overnight Shift Considerations: For 24/7 or overnight shifts, you will need to adjust the logic. Standard practice is to record overnight shifts (e.g., Tuesday 22:00 to Wednesday 08:00) on the day they begin.

Solution 2

Use Power Query and Power Pivot for Complex Schedules

For 24/7 schedules with irregular working hours where array formulas become too complex, use Power Query to transform and summarize the data.

Efficient Spreadsheet Management

Build Complex Heat Maps and Formulas in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array functions including SUMPRODUCT, IFERROR, and text extractions. You can effortlessly manage staff schedules, calculate team ratings, and design visual hourly heat maps.

1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing Spreadsheet document to set up your team schedule.
2. Input the SUMPRODUCT Formula: Select the target cell for your hourly bucket and enter the exact SUMPRODUCT formula used in Excel. The syntax is fully supported.
3. Apply Conditional Formatting: Highlight your calculated time buckets, click on 'Conditional Formatting' in the Home tab, and apply a Color Scale to instantly turn the data into a visual heat map.

100% compatibility with Microsoft Excel formulas and .xlsx formatsLightweight performance perfectly suited for large scheduling datasetsBuilt-in conditional formatting tools to easily visualize your hourly heat mapsFamiliar interface allowing immediate productivity without a learning curve

microsoft office alternative - wps office

Frequently Asked Questions

How do I handle overnight shifts that cross past midnight?

Record overnight shifts on the day they start. For example, a Tuesday 22:00 to Wednesday 08:00 shift should be recorded under Tuesday. You may also need to update your formula logic using IF statements to handle end times that are numerically smaller than start times.

Why is my SUMPRODUCT formula returning an error instead of calculating the ratings?

This usually happens if the text strings extracted by LEFT and RIGHT cannot be converted to numbers using the VALUE function. Ensure your schedule cells follow a strict time format (like '09:00-17:00') without extra spaces. Also, ensure the headers in your rota exactly match the labels in your ratings table.

Can I combine multiple skills into one time bucket?

Yes. You can keep multiple skills in a separate ratings table and use functions like CONCATENATE or TEXTJOIN alongside your SUMPRODUCT calculations to show combined ratings for each distinct skill during the same time bucket.

Excel & Data Problems