array function with SUM

Originally Posted by 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)?

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

Thanks for the reply.

Originally Posted by 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)

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.

Originally Posted by 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)

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!