minimum along rows
 

Hi,

Is it possible to sum up the minimums of each row in one line commmand?

E.g.

A B
1 5 6
2 2 0
3 1 3

Ans: 5 + 0 + 1

I've tried

{=sum(min(a1:a3,b1:b3))}

but it doesn't work.

Thanks

Try this:
=SUM(MIN(A1:B1),MIN(A2:B2),MIN(A3:B3))
--
Regards,
db

wrote in message
oups.com...
Hi,
Is it possible to sum up the minimums of each row in one line commmand?
E.g.
A B
1 5 6
2 2 0
3 1 3
Ans: 5 + 0 + 1
I've tried
{=sum(min(a1:a3,b1:b3))}
but it doesn't work.
Thanks

You have 2 numbers in column A and one in B.
Why Ans: 5 + 0 + 1?
Shouldn't be 1 + 0 + 1?

Bruno

Sorry for misleading, the first column indicate the row number.
Simply, I rewrite it as follows:

A B
5 6
2 0
1 3
: :

Expected result: 5 + 0 + 1 + ...

Bruno Campanini =E5=AF=AB=E9=81=93=EF=BC=9A

wrote in message
oups.com...
Hi,
Is it possible to sum up the minimums of each row in one line commmand?
E.g.
A B
1 5 6
2 2 0
3 1 3
Ans: 5 + 0 + 1
I've tried
{=3Dsum(min(a1:a3,b1:b3))}
but it doesn't work.
Thanks

You have 2 numbers in column A and one in B.
Why Ans: 5 + 0 + 1?
Shouldn't be 1 + 0 + 1?
=20
Bruno

I have tried a few formula options with no luck
however if a UDF would help

Function SumMin(myInfo As Range) As Double
Dim RowCount As Integer
Dim i As Integer
SumMin = 0
RowCount = myInfo.Rows.Count
For i = 1 To RowCount
SumMin = SumMin + WorksheetFunction.Min(myInfo.Rows(i))
Next i
End Function

As with much of my work it feels like brute force but seems to do the job
<g

if you need a primer on UDFs then
http://www.mvps.org/dmcritchie/excel/getstarted.htm
is a good place to start

hth RES

wrote in message
...
I have tried a few formula options with no luck
however if a UDF would help

Function SumMin(myInfo As Range) As Double
Dim RowCount As Integer
Dim i As Integer
SumMin = 0
RowCount = myInfo.Rows.Count
For i = 1 To RowCount
SumMin = SumMin + WorksheetFunction.Min(myInfo.Rows(i))
Next i
End Function

As with much of my work it feels like brute force but seems to do the job

Yes, it works fine!
My bruter force would omit SumMin = 0

Bye
Bruno

"Harlan Grove" wrote in message

The simplest way to handle your example data (or any 2-column
comparison) would be to use an array formula like

=SUM(IF(A1:A3<B1:B3,A1:A3,B1:B3))

Getting exotic, if you wanted the sum of the minimum values from each
row in the multiple (2) column range Rng, you could use a formula like

=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng))

The second formula doesn't work with me; I get #DIV/0!
What's wrong?

Bruno

Bruno Campanini wrote...
"Harlan Grove" wrote in message
....
=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng))

The second formula doesn't work with me; I get #DIV/0!
What's wrong?

If you have any blank cells in Rng, you'd need to change the formula to

=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng&""))

"Harlan Grove" wrote in message
oups.com...
Bruno Campanini wrote...
"Harlan Grove" wrote in message
...
=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng))

The second formula doesn't work with me; I get #DIV/0!
What's wrong?

If you have any blank cells in Rng, you'd need to change the formula to

=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng&""))

I didn't have any blank cells in Rng.
But now the new formula works fine.

Many thanks
Bruno

Harlan
I have tried to work out how your formula works and am struggling

=SUMPRODUCT(Rng,(COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),0,1,
COLUMNS(Rng)),"<"&Rng)=0)/COUNTIF(OFFSET(Rng,ROW(Rng)-CELL("Row",Rng),
0,1,COLUMNS(Rng)),Rng&""))

So far I think it creates an array the size of the range that identifies
(1 or 0) the lowest number (or if n multiples uses a fraction 1/n)
This is then Sumproducted with the original array.

Please could you explain how the two COUNTIF(OFFSET()) functions are
constructed and how they work

many thanks
RES

Harlan
thanks for taking the time to explain this.
there are a few new elements for me to use. My initial efforts to solve
the op's question had lead me down OFFSET with an array call. However, as
you point out, MIN does not work.
Therefore, I now understand the use of the two COUNTIFs to mimic this
action.
I did solve the problem with a brute force UDF but I do like the idea of
using worksheet functions when I can.