Summing columns based on matched values

Folks,

I want to sum columns (C,E,G) for each row if the lookup value (for columns B,D,F) matches a separate list without adding separate IF conditions for each column.

I need formulas (arrayed INDEX/MATCH perhaps..) for Column I and J of the attached.

Try:

=SUM(IF(ISTEXT(LOOKUP($B2:$F2,$K$1:$L$13)),IF(LOOKUP($B2:$F2,$K$1:$L$13)=I$1,$C2:$G2)))

CSE confirmed and copied down and over

or better:

=SUMPRODUCT(--(LOOKUP($B2:$F2&"",$K$1:$L$13&"")=I$1),$C2:$G2)

Regular entered.

Both formulas work great..!

just one thing that baffled me ...why is the lookup range $K$1:$L$13 and not $K$2:$L$13?

Because the formulas are also trying to lookup the numbers, and if we go from L2, they would get back #N/A! errors, so we add the empty cells above, so the numbers return the null from L1 since numbers are less than text in the sense of LOOKUP function, so last value smaller than or equal to the number is the blank cell at the top. That, and adding the nulls to the range references in the Sumproduct formula helps eliminate any #N/A errors, making the data "clean" for Sumproduct to work...

Cheers NBVC..that rounds off another great day of learning!

another option:

Please Login or Register  to view this content.