3. Calculate Weighted Injury Scores Using SUMPRODUCT and Lookup Tables

Why SUMPRODUCT fits a multi-injury record

A single crash incident rarely maps to a single injury. One record may contain a fatality, two severe injuries, and three minor injuries at the same time. If the scorecard stores each injury type in its own column, the weighted score for that record is the sum of count × weight across all injury categories. SUMPRODUCT is the natural Excel function for this because it multiplies two equally sized ranges element by element and returns the sum of those products in one formula — no helper columns, no nested IF chains, and no risk of a category being silently skipped.

The key design decision is to keep the weights in a lookup table rather than hard-coding numbers inside the formula. When the scoring policy changes (for example, a fatal injury moves from 10 points to 12), you edit one cell in the lookup table and every incident recalculates. This separation of data from policy is what makes the engine maintainable across the 30-case course.

Building the lookup table

Place the weight table on a dedicated sheet named Weights. The order of rows does not matter for SUMPRODUCT, but the column order must match the incident sheet exactly, because the two ranges are multiplied position by position.

On the incident sheet, columns D–G hold the counts of each injury type per record. Row 1 is the header row; data starts at row 2. The weight values live in Weights!$B$2:$B$5 in the same order (Fatal, Severe, Moderate, Minor).

The scoring formula

In cell H2 of the incident sheet, enter:

=SUMPRODUCT(D2:G2, TRANSPOSE(Weights!$B$2:$B$5))

Because D2:G2 is a horizontal range and the weight column is vertical, TRANSPOSE aligns them into the same orientation before multiplication. If you prefer to avoid TRANSPOSE (it can be slower on very large sheets and requires array entry in older Excel versions), store the weights as a horizontal row instead — for example Weights!$B$1:$E$1 — and use the simpler form:

=SUMPRODUCT(D2:G2, Weights!$B$1:$E$1)

Both formulas return identical results. The horizontal-weight version is the recommended default for this course because it is shorter, faster, and does not depend on array-entry behavior.

Worked example

Consider three incident records with the following counts:

INC-001 scores highest because a fatality dominates the weighting even though it also has the most minor injuries. This is the intended behavior: the weight table encodes the severity policy, and SUMPRODUCT applies it uniformly to every record regardless of how many injury types are present.

Handling records with multiple injury types correctly

The most common failure mode is a formula that only looks at one column, or that uses SUMIF against a single category. Those approaches undercount records with mixed injuries. SUMPRODUCT avoids this because it processes all four columns in a single pass. Two safeguards keep it correct:

• Blank cells: SUMPRODUCT treats empty cells as zero, so a record with no minor injuries still scores correctly. Do not replace blanks with text such as "N/A" — text in a numeric range causes #VALUE!.
• Column alignment: if someone inserts a column between D and G on the incident sheet, the range D2:G2 silently shifts and the weights no longer match. Protect the incident sheet structure or add a validation check (see below).

Validating the engine

Add a control cell that recomputes the total score a second way and compares it. On a Checks sheet:

=SUM(Incidents!H2:H1000) - (SUM(Incidents!D2:D1000)*Weights!B1
 + SUM(Incidents!E2:E1000)*Weights!C1
 + SUM(Incidents!F2:F1000)*Weights!D1
 + SUM(Incidents!G2:G1000)*Weights!E1)

A result of 0 confirms that the row-level SUMPRODUCT scores aggregate to the same total as a column-by-column weighted sum. Any non-zero value points to a misaligned range, a text value in a count column, or a weight edited in one place but not the other.

Filling the engine down the sheet

With H2 correct, select H2 and fill down to the last data row (for example H2:H1000). Each row now carries its own weighted score. Because the weight range is absolute and the count range is relative, every row multiplies its own counts by the same policy weights. This is the single formula that the later star-rating lookup in case 4 will consume — the scorecard's downstream logic depends on this column being correct and consistently aligned.