If you don't want to use a UDF, instead of a single formula consider using
one or two helper columns, all sorts of ways!
Here's another UDF, should be reasonably fast even with largish data sets
Function MatchTest(rng1 As Range, rng2 As Range) As Currency
Dim i As Long, j As Long
Dim cnt1 As Long, cnt2 As Long
Dim tot As Currency
Dim arr1, arr2
arr1 = rng1.Value
arr2 = rng2.Value
cnt1 = UBound(arr1)
cnt2 = UBound(arr2)
For i = 1 To cnt1
tot = tot + arr1(i, 2)
Next
For i = 1 To cnt2
tot = tot + arr2(i, 2)
Next
For i = 1 To cnt1
For j = 1 To cnt2
If arr1(i, 1) = arr2(j, 1) Then
If arr2(j, 2) < 0 Then
Exit For
End If
End If
Next
If j < cnt2 Then
tot = tot - arr1(i, 2)
End If
Next
MatchTest = tot
End Function
Sub test()
Dim rng1 As Range, rng2 As Range
Set rng1 = Range("A1:B6")
Set rng2 = Range("E1:F6")
MsgBox MatchTest(rng1, rng2)
End Sub
Regards,
Peter T
"DocBrown" wrote in message
...
I've figured out a solution. I wrote a function that performs the sum that
I
want and I call that from the formula for the cell. It seems to work,
almost.
The function causes #VALUE! to be displayed. F9 clears it and displays the
correct value.
I have a second function referenced in a different cell that also displays
#VALUE!. I have no idea why that is.
I'd still like to see a solution that doesn't use a UDF.
John
Here's the function. Note the cell references are different because these
are the columns on my live workbook. Comments on this function are
welcome.
Function SubTotalMatch(rngSource As Range, rngMatch As Range, rngSubTotal
As
Range) As Currency
Application.Volatile
Dim cCell As Range
Dim cellIndex As Integer
Dim srcStr As String
Dim Total As Currency
Total = 0
If rngSource.Count < rngMatch.Count Or rngSource.Count <
rngSubTotal.Count
Then
SubTotalMatch = 0
Exit Function
End If
For cellIndex = 1 To rngSource.Count
srcStr = rngSource.Cells(cellIndex).Value
Set cCell = rngMatch.Find(srcStr, LookIn:=xlValues)
If Not cCell Is Nothing Then
If cCell.Value < "" And cCell.Offset(0, 3).Value = "" Then
Total = Total + rngSubTotal.Cells(cellIndex).Value
End If
End If
Next cellIndex
SubTotalMatch = Total
End Function
"p45cal" wrote:
DocBrown;490117 Wrote:
Hi guys, I appreciate your help on this.
Hi p45cal,
Your solution is working except that it doesn't pick up the $120 value
in
B5. In that case, the value 120 in A5 is found in col E, but since
there's no
entry in F3, I need that value to be added also.
I'm thinking of a MATCH or SUMIF or something like that. Any ideas?
Here's NEW data that has all the conditions, I think.
A B C D E F
1 100 $100.00 100 $300.00
2 120 $121.00 120
3 $75.00 130 $200.00
4 100 $25.00 140 $250.00
5 140 $218.00
The total should be:
Sum(F1:F5)+ ( B3 ) + ( B2 ) = 750.00 + 75.00 + 121.00 = 946.00
B2 is added because no value is in F2,
B3 is added because no id is in A3.
Note that all values present in A are found in E. Extra values may be
present in E.
John I have been looking, but have not yet seen the light. I urge you
to
take up my suggestion in post#6 of this thread, it's probably the
quickest way. I'll have another look at it later tomorrow.
--
p45cal
*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=134340