Formula Array with MIN

[PeterWilliams]

Hi, I need to use a formula array to sum a list of numbers, which are the minimums from two columns. However, using MIN inside my formula array seems to return just the MIN over the whole set.

For example,
A B
1 5
4 6
3 2

So what I actually want to sum is 1, 4 and 2.

Logically, I thought the function should be:

{=SUM(MIN(A1:A3, B1:B6))}
but this just returns the min of all numbers.

Can anyone suggest how I might do this??? Thanks in advance.
Peter.

[shg]

Try {=SUM(SMALL(A1:B3, {1,2,3}))}

[PeterWilliams]

A better example:
A B C
Data <Min(A, B)
3 5 3
6 1 1
8 6 6
7 9 7


I need to use just Col A and B to sum 3, 1, 6 and 7.

[shg]

Perhaps you could just explain rather than provide examples. From your example, it looks like to want to sum the minimum values in each row, not column.

[ptm0412]

It should be = {Sum(if(A1:A4<B1:B4,A1:A4,B1B4)}
Surely Ctrl+Shift+Enter

[PeterWilliams]

Hopefully the attached will make things clearer.

[Portuga]

You have it.
ptm0412 already gave you the solution.

[MJ100]

The solution given by ptm0412 is not sufficient as I am dealing with huge data sets. Using the "if" suggestion would generate large formulas that exceed the excel cell limits. Thus, I require a way to be able to sum a set of minimums (that are not as simple as the example given). There is obviously ways around this by using user defined functions and re-arranging the data sets, however, as mentioned this is across extremely large data sets and so would get very messy very quickly. Hence, I would like, if it is possible, to use a formula array that sums a group of minimums. The question is why does minimum not work as expected in formula arrays and is there a different term, like "small" or something that needs to be used instead?

As described in a previous posting, I want to be able to sum the minimum of each row across a group of columns. For example

Row A B min(A,B)
1: 1 2 1
2: 5 4 4
3: 4 3 3

Using only columns A and B, I would like to sum the minimum of each row, i.e. 1+4+3 to get 8, however when the array formula (SUM(MIN(A1:A3,B1:B3)) is used I get 1.

Any suggestions would be greatly appreciated. Thanks in advance.

Maria

[daddylonglegs]

MIN can only return a single result not an array. You'll have similar problems with SMALL. You can get the result you want with this formula

=SUMPRODUCT(SUBTOTAL(5,OFFSET(A1:B1,ROW(A1:A3)-ROW(A1),0)))

which you should be able to modify for any larger range, e.g. if you have the range A1:J10 and you still want to sum the minimum for each row

=SUMPRODUCT(SUBTOTAL(5,OFFSET(A1:J1,ROW(A1:A10)-ROW(A1),0)))