SUMIF (or something) over a wide range of columns-CodePudding

I've got a massive parts spreadsheet that I'm trying to simplify. Various parts could be included in number of locations, which I would like to add up to a single list. The attached file is just an example using reindeer.

This is doable with using a bunch of SUMIF statements added together, but not practical due to the range of columns I need to include. There's gotta be a better way!?

CodePudding user response：

=SUMPRODUCT(--($D$4:$J$11=$A4),$E$4:$K$11))

SUMPRODUCT can do that. Make sure the second range shifts one column, but has equal count of columns (and rows).

CodePudding user response：

You can use simply the SUM:

=SUM((D$4:$D$11=A4)*$E$4:$E$11,($F$4:$F$11=A4)*$G$4:$G$11, etc.)

where in etc you can put any range you want. If you don't use 2021/365 version, you must confirm the formula with CTRL SHIFT ENTER.