Array function for summing the minimum between two columns

**shwayze24** · 02-03-2011, 11:52 AM

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

**scottylad2** · 02-03-2011, 11:55 AM

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?

**shwayze24** · 02-03-2011, 12:00 PM

Originally Posted by scottylad2

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

I did not intend for it to be displayed in such a nature. In essence, i need a dymanic funtion that is saying sum(min(A1:B1),min(A2,B2).....min(Ax,Bx)) which is dymanic and allows me to add rows

**DonkeyOte** · 02-03-2011, 12:07 PM

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

Please Login or Register  to view this content.

adjust reference to A1:B6 as necessary

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

**shwayze24** · 02-03-2011, 12:14 PM

Thank you very much. Would it be possible for you to help explain the logic of what that function is doing to help my understanding?

**DonkeyOte** · 02-03-2011, 12:17 PM

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.

**shwayze24** · 02-03-2011, 12:24 PM

Thank you very much, this is very helpful. I did not believe I could Min seperately C and Sum results because C is a formula :

=IF(IF(A1="",B1,IF(B1="",A1,IF(AND(A1="",B1=""),"","$"&A1&" / "&"$"&B1&" ")))=0,"",IF(O6="",P6,IF(A1="",B1,IF(B1="",A1,IF(AND(A1="",B1=""),"","$"&A1&" / "&"$"&B1&" "))))

To display A1 / B1