Sum values with lookup number specified by another matrix

Hello, long-time reader, first-time poster,

I'm not sure if I can succinctly describe this issue, but a workbook is attached in case I fail.

I have a two-row matrix with variables LookupCode and ValuetoSum.

I also have a matrix LookupCode by SumCategory, with X's to indicate the LookUpCodes relevant to each value of SumCategory.

For each value of SumCategory, I want a sum of only the ValuetoSums associated with the LookupCodes indicated by this matrix.

Notes: I have to do this many times over, with various ValuetoSum vectors, so adding other columns or rows to the worksheet would not be elegant. I strongly prefer a solution within Excel without using Visual Basic.

It would be great if someone could help with this. Thank you very much!

You can put this formula in cell F3:

=HLOOKUP(A3,$H$3:$P$4,2,0)

and copy it down into F4:F11. Then you can put this formula in C15:

=SUMIF(C$3:C$11,"X",$F$3:$F$11)

and copy this across into D15:E15.

Hope this helps.

Pete

Hi,

Thank you both for the ideas. The sections of the documents I'm working with are part of large and complex Excel files, so I was hoping to find some sort of clever INDEX MATCH solution or some such that wouldn't require me to create new rows or columns, or change the structure of existing ones. I think such a solution probably doesn't exist, or would result in a longer and more complex formula than it's worth it to implement. Your suggestions were helpful in working out a feasible solution.

Michael