It's hard to understand what you want to do, although it sounds a bit like a
SUMIF - you're summing one column based on the value in another but the
column to be summed is variable?

Perhaps that's a bit simplistic but just to start the ball rolling then,
using INDEX as RagDyeR suggests

=SUMIF(A2:A100,"x",INDEX(B2:AE100,0,MATCH("y",B1:A E1,0)))

this will sum a variable column (based on matching a value in the header -
"y") when column A contains "x".

Is that close to what you want or nowhere?

"scabHead" wrote:

I am struggling trying to get offset to work in an array formula.

Let me give some background. My spreadsheet has a matrix portion of it
where 3 categories of data are stored for each of 10 weeks. So there are 30
columns of data. I have been using array formulas in order to sum the like
columns since each sum should only count 10 of the 30 columns. Everything
works great with that. I have one more calculation I am trying to do though
where I am struggling.

Essentially i want the formula to look in a cell, use the value stored there
as the row offset data in an OFFSET function to retrieve another value and
then have the array function sum that. The offset function seems to fail
whenever the formula is entered as an array formula. As best I can tell, the
issue is that the row offset reference in the OFFSET function is now a range
(like K21:AN21) instead of a single cell. My hope was the array function
would cycle the cell entries just like it does for other portions of the
formula. It appears that the array formula approach is not working to get it
to cycle or something.

Any suggestions? I would really like to avoid having intermediate
calculations on the spreadsheet. My current workaround is to have the offset
lookups performed on a range of hidden cells and I sum those. But that is
certainly less than elegant. Am I trying to go too far with array formulas
here?