array function with SUM

[jprep]

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!

[ChemistB]

You lost me on "Hello"
So in your example, you would expect it to sum the first column (as that is the only column where the first row is < 2)?

[jprep]

You lost me on "Hello"
So in your example, you would expect it to sum the first column (as that is the only column where the first row is < 2)?

I would want it to sum the first two columns in this example, so <= 2.

Thanks for the reply.

[daddylonglegs]

If the top row is H21:K21 then try this formula

=SUM(IF(H21:K21<=I20,H22:K25))

confirmed with CTRL+SHIFT+ENTER

....or as long as all values in H22:K25 are numeric you can avoid CSE and use SUMPRODUCT with

=SUMPRODUCT((H21:K21<=I20)*H22:K25)

[jprep]

If the top row is H21:K21 then try this formula

=SUM(IF(H21:K21<=I20,H22:K25))

confirmed with CTRL+SHIFT+ENTER

....or as long as all values in H22:K25 are numeric you can avoid CSE and use SUMPRODUCT with

=SUMPRODUCT((H21:K21<=I20)*H22:K25)

Thanks!

I realized that I didn't include something. In my example, I eventually want to look at the top row (doing <= 2) and the leftmost column ( maybe >= 3 for instance).

I can't use an index match formula, since that only returns one value - I want to sum up many values, such as the bottom-left area of numbers: 900, 100, 400, and 500.

Many thanks.

[daddylonglegs]

You can use the same principle to look at both the top row and leftmost column, e.g. assuming leftmost column is G22:G25 you can use this array formula

=SUM(IF(H21:K21<=2,IF(G22:G25>=3,H22:K25)))

confirmed with CTRL+SHIFT+ENTER

....that will sum all values in H22:K25 where both conditions are met for header row and leftmost column.

....or SUMPRODUCT for the same result....

=SUMPRODUCT((H21:K21<=2)*(G22:G25>=3),H22:K25)

[jprep]

You can use the same principle to look at both the top row and leftmost column, e.g. assuming leftmost column is G22:G25 you can use this array formula

=SUM(IF(H21:K21<=2,IF(G22:G25>=3,H22:K25)))

confirmed with CTRL+SHIFT+ENTER

....that will sum all values in H22:K25 where both conditions are met for header row and leftmost column.

....or SUMPRODUCT for the same result....

=SUMPRODUCT((H21:K21<=2)*(G22:G25>=3),H22:K25)

WOW, that worked! And yet it was so simple compared to what I was trying to do.

I seriously owe you a beer, or at the very least, $20. Thanks!