Using multiple columns in single cell formula

Hello,

So this is easiest explained when looking at my excel sheet. In the bottom grouping of cells, I'm keeping a tally of all product. I grab the totals from the top 2 groups and then I want to subtract out anything that gets put in my 'active' columns (A14:F39).

My current formula follows along this same premise:

Please Login or Register  to view this content.

I've even tried the whole ctr+shift+enter for array formulas, but I'm completely unfamiliar with that so it was a shot in the dark that failed.

Just look at the forumlas in K17:Q22 and please tell me where I screwed up.

Thank you.

You probably want:

=B4+J4-SUMPRODUCT(--($A$14:$A$39=$H17),--($B$14:$B$39=K$14),$D$14:$D$39)

copied down and across

It coerces TRUE/FALSE results to 1/0 for math purposes.