There's a moment every spreadsheet user knows: the formula looks right, the logic feels sound, and yet the number staring back at you is stubbornly wrong. That's exactly where this user finds themselves, and the frustration is justified. But here's the thing: SUMPRODUCT isn't broken. The issue is that it's doing exactly what it was asked to do, and the real problem is a mismatch between how the formula is written and how the data is structured. When you point SUMPRODUCT at a range that includes merged cells, helper rows, or misaligned criteria, it doesn't fail loudly. It fails quietly, returning a plausible but incorrect result. That's the most dangerous kind of error, because it feels like progress.
The user's formula multiplies three conditions together: department match, service match, and the actual values. But the service criteria is pointing at a row that contains merged headers and helper entries. In Excel, merged cells only retain a value in the top-left cell. The rest of the merged range holds nothing, or worse, holds a value that repeats from a helper row. So when the formula checks `$C$6:$N$6=B$1`, it's evaluating against a row where most cells are empty or contain the same repeated department name. That's why changing the service name in A4 sometimes doesn't change the result at all. The criteria isn't being ignored. It's just matching against cells that don't vary the way you expect. This isn't a SUMPRODUCT limitation. It's a data layout problem wearing a formula's clothing.
The practical takeaway is straightforward: before you debug the formula, fix the structure. Unmerge those header cells. Put a single service name in each column, and if you need to group services later, add a separate column for the group. Don't rely on helper rows that visually repeat values across merged ranges. If you must keep the layout, use SUMIFS with explicit ranges instead of SUMPRODUCT, but even then, you'll still need clean, unmerged cells for the criteria to work reliably. The goal isn't to force a formula to work. It's to design the sheet so the formula doesn't have to guess. In this case, the user's instinct to use SUMPRODUCT wasn't wrong. The data just wasn't ready for it.
So here's the concrete point: stop treating SUMPRODUCT as a magic wand and start treating it as a tool that mirrors your data's integrity. If the result stays constant when you change criteria, check the range you're feeding it. If the numbers are close but off, check for merged cells and hidden repeats. The formula isn't the enemy. The structure is. Fix that, and the 300 becomes 599, not because the formula changed, but because the data finally matches what the formula was always trying to calculate.