Formula for summing vertically based on horisontal condition

[enfantter]

Hi all,

I trying to create an excel formula which is able to to sum a column(vertically) if two condition are fulfilled - these conditions are enteres in column 1 and 3 in the range ...
here is the code

Function betsum(rg, navn, status)
Dim rg As Range
Dim navn As String
Dim status As String
For Each Row In rg
For i = 0 To rg.lastrow.RowCount
If Cells(i, 1) = navn And Cells(i, 3) = status Then
tilsum(i) = Cells(i, 2).Value
End If
Next i
Next Row
betsum() = Sum(tilsum(i)) 'for all i's

End Function

It appears that my code has a flaw ... but i cannot find it - anybody sees it?!

[Krishna Kumar]

Hi,

=SUMPRODUCT(--(A2:A100=navn),--(C2:C100=Status),B2:B100)

where navn and status are criterias.

HTH

[Bob Phillips]

I think this is what you wnated in code



Function betsum(rg, navn, status)
Dim tilsum As Double
Dim nRow As Range
For Each nRow In rg
If nRow.Cells(1, 1).Value = navn And nRow.Cells(1, 3).Value = status Then
tilsum = tilsum + nRow.Cells(1, 2).Value
End If
Next nRow
betsum = tilsum
End Function

[enfantter]

ok, how are the hyphens to be understood?! (--)

[Bob Phillips]

They are just coercing an array of TRUE/FALSE to an array of 1/0, so that the SUM part can add them up. -TRUE=-1, --TRUE = 1, -FALSE=0, --FALSE = 0.

[enfantter]

exactly xld tnx again!