Table with multiple Header Rows to make a SUMProduct formula work?

I'll tackle question B first: Yes, there is a better way: get rid of the colors, the merged cells up at the top, all of the things that you've done to make it "pretty." Those may be pleasing to the human eye, but they can inhibit Excel--merged cells in particular can be problematic.  Basic point: hold off on those features until you get it working. Especially if this is where the "raw" or "input" data is going to go, you don't ever need to make it pretty. Save that for the output end of things, and even then, don't pour energy into beautification until you get the functionality you want.

I'd like to address question A as well, but frankly, at my advanced age (octogenarian) with only a laptop at my disposal at the moment, I can't quite make out the headings, sub-headings, sub-sub-headings. Since it's devoid of data anyway, it seems unlikely that there's anything totally confidential, would you be willing to share not an image but the actual workbook, so we could (1) look at it in real life, and (2) possibly demonstrate some ways that you could make each header cell a single cell with fully adequate info in it.

Could you also give the context here. That might make a difference. How is this going to be used? What's the business or professional purpose to be served? Will data be added every day, every week (etc)? in the various rows? Etc., etc.

I hinted at it above, but let me make this explicit: it's often useful to plan to separate the raw or input data from the output itself, actually placing those two "components" on separate sheets. You're asking about SUMPRODUCT, leading me to believe you will be extracting selected data to produce a summary of some kind...that would go along with creating a separate sheet in the workbook to display that summary.