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

wrote...
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
....

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))

"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

wrote...
....
=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

The key is the OFFSET calls. They return what are effectively arrays of
range references, in this case arrays of references to each row in Rng.
That's due to the 2nd argument which evaluates to an array of serial
numbers from 0 to N-1 where N is the number of rows in Rng. COUNTIF,
SUMIF, N and T seem to be the only functions that can handle such
arrays. COUNTIF and SUMIF return array results as if their (scalar)
criteria argument had been applied to each range in the array of range
references.

The first COUNTIF call returns an array of nonnegative numbers in which
zero corresponds to the max value (the value for which there are no
values strictly larger). The equality test after boolean to numeric
conversion then has ones for max values and zeros for smaller values.
The second COUNTIF call in the denominator returns the number of max
values.

As for the interaction of OFFSET-generated arrays of ranges and an
array criteria argument to COUNTIF, it seems to work when the arrays
conform. The return value is the same size as the criteria argument and
is equivalent to applying each separate range to the entire criteria
array.

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.