Hello all, first time poster here.

I have output from a query that looks like this (simplified version):

1 2 3 4
1 100 200 300 400
2 500 600 700 800
3 900 100 200 300
4 400 500 600 700

I want to use an array formula to sum certain parts of the data, and I want to be able to look at the top row. This is the formula I'm thinking of, but it returns #VALUE!:

{=SUM((OFFSET($H$22:$K$25,20-ROW($H$22:$K$25),0)<=I20)*($H$22:$K$25))}

I wanted to use offset() on each of the 16 cells, make it look at the top row, and see if the number is less than 2 (that's what cell I20 is).

The output of the offset() function is {1;0;0;0}. It apparently starts at the cell with 100, grabs 1 (one cell above it), then looks 2, 3, and 4 cells above it.I want the offset function to output this (when using F9 in the formula):

{=SUM(({1,2,3,4;1,2,3,4;1,2,3,4;1,2,3,4;}<=I20)*($H$22:$K$25))}

Using that (when manually typed in) will give the desired output.


Am I approaching this the wrong way? Thanks for the help!