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