minimum along rows

[hochungwa]

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

 

[Guest]

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

 

[Bruno Campanini]

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

 

[hochungwa]

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 寫é“：

 

[Robert_Steel]

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

 

[Bruno Campanini]

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]

(e-mail address removed) 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))

 

[Bruno Campanini]

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

 

[Harlan Grove]

Bruno Campanini wrote...

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

 

[Bruno Campanini]

Bruno Campanini wrote...

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

 

[Robert_Steel]

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 Grove]

(e-mail address removed) wrote...
....

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.

 

[Robert_Steel]

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.