Sum minimum of corresponding column values

Question

I have two columns with numbers. I would like to calculate in a separate cell a sum. The sum will include the minimums of corresponding cells in these two columns.

Example:

        A  |  B
       --------
[1]     1  |  2
[2]     4  |  3
[3]     0  |  1
[4]     5  |  5

I need a formula that would calculate in a cell the sum of 1 + 3 + 0 + 5 where

* 1 is the MIN(A1,B1), 
* 3 is the MIN(A2,B2) 
* 0 is the MIN(A3,B3)
* 5 is the MIN(A4,B4)

Is that possible in a single formula (independent of #rows)?

Working with LibreOffice Calc at the moment, but Excel solutions more than welcome.

Answer 1

You can use an array formula (doc for Libre Office and for Excel) for this:

=SUM(IF(A1:A4<B1:B4, A1:A4, B1:B4))

Confirm by Ctrl+Shift+Enter instead of simple Enter.

Answer 2

Well, you can do this with a formula but it's not very scalable. For example, you can do this:

=SUM(MIN(A1:B1),MIN(A2:B2),MIN(A3:B3), MIN(A4:B4))

that will work in the case you described. However, I appreciate that if you have a large number of rows then this won't scale nicely. In that case, I think you'll need a VBA macro as I can't see a way to do this. I'm prepared to be corrected though by some Excel formula guru.

For a VBA solution, you can try the following (found in the OzGrid forums):

Function sumOfMax(ByVal inputRange As Range, Optional doMin As Boolean) As Double
    Dim inRRay As Variant
    Dim currentMax As Double
    Dim i As Long, j As Long

    Set inputRange = Application.Intersect(inputRange, inputRange.Parent.UsedRange)
    If Nothing Is inputRange Then Exit Function
    inRRay = inputRange.Value
    For i = 1 To UBound(inRRay, 1)
        currentMax = Val(inRRay(i, 1))
        For j = 1 To UBound(inRRay, 2)
            If (Val(inRRay(i, j)) > currentMax) Xor doMin Then currentMax = Val(inRRay(i, j))
        Next j
        sumOfMax = sumOfMax + currentMax
    Next i
End Function

When set to TRUE, the optional parameter calculates the minimums as opposed to the maximums that this macro calculates by default. In your example, you would need to call it like this:

=sumOfMax(A1:B4, TRUE)

Just remember you need to place the code in a standard code module.