Thread: Array function for summing the minimum between two columns

Hey,

Im new here and Im looking to sum the minimum between two columns over a large number of rows. For instance Column A would have the values 7, [ ], 9 and Column B would have the values 5, 8 , 10 and Column C would be displaying 7 / 5, 8, 9 / 10....at the bottom of Column C would be a total summing 5+8+9=22....any help would be greatly appreciated. Thanks in advance

Originally Posted by shwayze24

Hey,

Im new here and Im looking to sum the minimum between two columns. For instance, my sheet looks like this:

Columns A and B are hidden, column c is displayed
A B C
5 7 5 / 7
6 6
3 3
Total:14

any help would be appreciated, thanks

It looks to me as if the min in both Col A and B is 3, can you explain how you get to 14?

if you're saying you want to do this without use of C1:Cn (individual Mins) then:

B7:
=SUMPRODUCT(SUBTOTAL(5,OFFSET($A$1:$B$1,ROW($A$1:$B$6)-ROW($A$1),0)))

adjust reference to A1:B6 as necessary

If you want to use Column C just add the MIN tests and SUM.

The OFFSET takes a 1x2 range (A1:B1) and offsets from that range by 0 to n-1 rows where n is determined by last row in range (6)

In essence this means you end up with 6 different ranges

A1:B1 offset by {0;1;2;3;4;5}

A1:B1;A2:B2;A3:B3;A4:B4;A5:B5;A6:B6

to each of these ranges we apply the SUBTOTAL function specify function_num as 5 (MIN)

We use SUMPRODUCT to aggregate the 6 MINs

Word of warning - this is inefficient & Volatile (OFFSET) - it would be far more efficient to store each MIN separately (C) and SUM results.