I'm pretty sure this can be done with sumproduct, but I can't quite get my
head around it.

I have two tables, Tbl1 A & B, Tbl2 E & F. Both are limited to 15 rows.

A B C E F
1 100 $100.00 100 $300
2 110 $50.00 130 $200
3 100 $25.00 120
4 $75.00
5 120 $120.00

The entries in col E of tbl2 are unique. What I'd like to do given this data
is:

- Sum tbl2 col F,
- for each row in Tbl1, if item A is "" or isn't in tbl2 then add B value to
the sum.
- for each row in tbl1, if item A is found in tbl2 but F is "" then add B
value.

Given the data shown the sum would become:
sum = (F1 + F2) + B2 + B4 + B5 = $300.00 + $200.00 + $50.00 + $75.00 + $120.00

Thanks a bunch,
John

If I follow,

=SUM(F1:F5)+SUMPRODUCT((B1:B5)*(COUNTIF(A1:A5,E1:E 5)=0))

Regards,
Peter T

"DocBrown" wrote in message
...
I'm pretty sure this can be done with sumproduct, but I can't quite get my
head around it.

I have two tables, Tbl1 A & B, Tbl2 E & F. Both are limited to 15 rows.

A B C E F
1 100 $100.00 100 $300
2 110 $50.00 130 $200
3 100 $25.00 120
4 $75.00
5 120 $120.00

The entries in col E of tbl2 are unique. What I'd like to do given this
data
is:

- Sum tbl2 col F,
- for each row in Tbl1, if item A is "" or isn't in tbl2 then add B value
to
the sum.
- for each row in tbl1, if item A is found in tbl2 but F is "" then add B
value.

Given the data shown the sum would become:
sum = (F1 + F2) + B2 + B4 + B5 = $300.00 + $200.00 + $50.00 + $75.00 +
$120.00

Thanks a bunch,
John

DocBrown;487185 Wrote:
- for each row in Tbl1, if item A is "" or isn't in tbl2 then add B
value to the sum.
This formula might do this bit - but CHECK - I haven't thoroughly
checked it.

=SUMPRODUCT(--(NOT(COUNTIF($E$1:$E$15,$A$1:$A$15)0)),$B$1:$B$15 )

DocBrown;487185 Wrote:
- for each row in tbl1, if item A is found in tbl2 but F is "" then add
B value. Confirm that if there is more than one item A in tbl1 you want to add
all their values. This would arise if instead of $300 for item 100 in
tbl2 you had "". There are 2 values for item 100 in tbl1 ($100 and $25);
add them both?


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

Hi,

Thanks to both Peter T and p45cal! The COUNTIF function is the ticket I was
looking for. I need to do a little bit of a tweek to get it to fully work
with my requirements, but it's definitely put me on the right track.

Appreciated,
John

"p45cal" wrote:


DocBrown;487185 Wrote:
- for each row in Tbl1, if item A is "" or isn't in tbl2 then add B
value to the sum.
This formula might do this bit - but CHECK - I haven't thoroughly
checked it.

=SUMPRODUCT(--(NOT(COUNTIF($E$1:$E$15,$A$1:$A$15)0)),$B$1:$B$15 )

DocBrown;487185 Wrote:
- for each row in tbl1, if item A is found in tbl2 but F is "" then add
B value. Confirm that if there is more than one item A in tbl1 you want to add
all their values. This would arise if instead of $300 for item 100 in
tbl2 you had "". There are 2 values for item 100 in tbl1 ($100 and $25);
add them both?


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

I was looking at this today and struggled to find an elegant worksheet
formula solution. I've developed a working macro solution to help test
my miserable formula attempts but no real joy I'm afraid.. *::but::*:
There is someone who I think might help if he saw your problem. I
suggest posting at 'vbaexpress.com' (http://vbaexpress.com/forum) under
Excel help. With a bit of luck *xld* will see it - if anyone can solve
it, he can.
If you choose to do so, please state, at both sites (1) that you've
cross-posted, and (2) provide a link to the corresponding thread
(agaimn, at both sites).


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

Thanks p45cal,

I'll drop over there and take a look. If I post, I'll follow your suggestion.

The concept you provided gets me 75% there. The solution is still not adding
in the B5 cell. This is the case where the A5 matches the E3, but because F3
is empty, I want to add B5 to the total.

Ideas?

John

"p45cal" wrote:


I was looking at this today and struggled to find an elegant worksheet
formula solution. I've developed a working macro solution to help test
my miserable formula attempts but no real joy I'm afraid.. *::but::*:
There is someone who I think might help if he saw your problem. I
suggest posting at 'vbaexpress.com' (http://vbaexpress.com/forum) under
Excel help. With a bit of luck *xld* will see it - if anyone can solve
it, he can.
If you choose to do so, please state, at both sites (1) that you've
cross-posted, and (2) provide a link to the corresponding thread
(agaimn, at both sites).


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

You said the result needed to be like this
= (F1 + F2) + B2 + B4 + B5
= 300.00 + 200.00 + 50.00 + 75.00 + 120.00
= 745

The Sumproduct/Countif formula I suggested returns 745 with your sample
data.

Regards,
Peter T


"DocBrown" wrote in message
...
Thanks p45cal,

I'll drop over there and take a look. If I post, I'll follow your
suggestion.

The concept you provided gets me 75% there. The solution is still not
adding
in the B5 cell. This is the case where the A5 matches the E3, but because
F3
is empty, I want to add B5 to the total.

Ideas?

John

"p45cal" wrote:


I was looking at this today and struggled to find an elegant worksheet
formula solution. I've developed a working macro solution to help test
my miserable formula attempts but no real joy I'm afraid.. *::but::*:
There is someone who I think might help if he saw your problem. I
suggest posting at 'vbaexpress.com' (http://vbaexpress.com/forum) under
Excel help. With a bit of luck *xld* will see it - if anyone can solve
it, he can.
If you choose to do so, please state, at both sites (1) that you've
cross-posted, and (2) provide a link to the corresponding thread
(agaimn, at both sites).


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

Peter T;488737 Wrote:
You said the result needed to be like this
= (F1 + F2) + B2 + B4 + B5
= 300.00 + 200.00 + 50.00 + 75.00 + 120.00
= 745

The Sumproduct/Countif formula I suggested returns 745 with your
sample
data.

Regards,
Peter TYes, Peter, it gives the same result for his example. But further
testing yields some odd results; If the ranges in your formula are
expanded to cater for the full 15 rows possible, then when you:
Add a new item in table 1 after the last entry, call it 101 and give it
the value $3 in the second column of table 1,
then add 101 after the last entry in column 1 of table 2 and leave the
value empty..

I -*think *-the answer should be 748, but your formula gives 673.


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

"p45cal" wrote in message
...

Peter T;488737 Wrote:
You said the result needed to be like this
= (F1 + F2) + B2 + B4 + B5
= 300.00 + 200.00 + 50.00 + 75.00 + 120.00
= 745

The Sumproduct/Countif formula I suggested returns 745 with your
sample
data.

Regards,
Peter TYes, Peter, it gives the same result for his example. But further
testing yields some odd results; If the ranges in your formula are
expanded to cater for the full 15 rows possible, then when you:
Add a new item in table 1 after the last entry, call it 101 and give it
the value $3 in the second column of table 1,
then add 101 after the last entry in column 1 of table 2 and leave the
value empty..

I -*think *-the answer should be 748, but your formula gives 673.


Indeed you are right, I see why it's wrong but trickier to fix than I
thought!
Of course would be much easier if the data is tidied up a little beforehand.

Regards,
Peter T

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

"Peter T" wrote:


"p45cal" wrote in message
...

Peter T;488737 Wrote:
You said the result needed to be like this
= (F1 + F2) + B2 + B4 + B5
= 300.00 + 200.00 + 50.00 + 75.00 + 120.00
= 745

The Sumproduct/Countif formula I suggested returns 745 with your
sample
data.

Regards,
Peter TYes, Peter, it gives the same result for his example. But further
testing yields some odd results; If the ranges in your formula are
expanded to cater for the full 15 rows possible, then when you:
Add a new item in table 1 after the last entry, call it 101 and give it
the value $3 in the second column of table 1,
then add 101 after the last entry in column 1 of table 2 and leave the
value empty..

I -*think *-the answer should be 748, but your formula gives 673.


Indeed you are right, I see why it's wrong but trickier to fix than I
thought!
Of course would be much easier if the data is tidied up a little beforehand.

Regards,
Peter T

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

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

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

DocBrown;493716 Wrote:
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


DocBrown,
could you clarify the relationship between your original
"Tbl1 A & B, Tbl2 E & F"
and the ranges
ngSource, rngMatch, rngSubTotal
in the code above?
Also should they be single columns?
I'm a bit puzzled with the 3 in:
And cCell.Offset(0, 3).Value


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

yea, the code is a bit obscure. *grin* I'm sure I can clean it up a bunch.

The test data columns correspond to the real data as follows:

A = E = rngSource
B = H = rngSubTotal
E = L = rngMatch
F = O = rngMatch.Offset( ,3)

I probably should pass in that 4th column, or a multi-column range. Yes,
they were intended to be single columns. As you see, the real data has other
columns between the columns I want to reference.

John

"p45cal" wrote:


DocBrown;493716 Wrote:
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


DocBrown,
could you clarify the relationship between your original
"Tbl1 A & B, Tbl2 E & F"
and the ranges
ngSource, rngMatch, rngSubTotal
in the code above?
Also should they be single columns?
I'm a bit puzzled with the 3 in:
And cCell.Offset(0, 3).Value


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

DocBrown;494694 Wrote:
yea, the code is a bit obscure. *grin* I'm sure I can clean it up a
bunch.

The test data columns correspond to the real data as follows:

A = E = rngSource
B = H = rngSubTotal
E = L = rngMatch
F = O = rngMatch.Offset( ,3)

I probably should pass in that 4th column, or a multi-column range.
Yes,
they were intended to be single columns. As you see, the real data has
other
columns between the columns I want to reference.

John

"p45cal" wrote:


DocBrown;493716 Wrote:
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


DocBrown,
could you clarify the relationship between your original
"Tbl1 A & B, Tbl2 E & F"
and the ranges
ngSource, rngMatch, rngSubTotal
in the code above?
Also should they be single columns?
I'm a bit puzzled with the 3 in:
And cCell.Offset(0, 3).Value


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: 'The Code Cage Forums - View Profile: p45cal'
(http://www.thecodecage.com/forumz/member.php?userid=558)
View this thread: 'Sumproduct forumla for complex sum. - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=134340)



DocBrown,
I tried your function on the original data but I think it gives wrong
results; I stepped through it and (if I've got the ranges correct)
couldn't find
a) where the code added the $75
b) where the code added $50 for the 110.
Below, I've tweaked your function and tacked '2' onto the end of its
name:
Function SubTotalMatch2(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
Dim srcValue
Total = 0

If rngSource.Count < rngMatch.Count Or rngSource.Count <
rngSubTotal.Count Then
SubTotalMatch2 = 0
Exit Function
End If

For cellIndex = 1 To rngSource.Count
srcStr = rngSource.Cells(cellIndex).Value
srcValue = rngSubTotal.Cells(cellIndex).Value
If srcStr = "" Then
Total = Total + srcValue
Else
Set cCell = rngMatch.Find(srcStr, LookIn:=xlValues)
If cCell Is Nothing Then
Total = Total + srcValue
Else
If cCell.Offset(0, 3).Value = "" Then
Total = Total + srcValue
End If
End If
End If
Next cellIndex

SubTotalMatch2 = Total ' + Application.Sum(rngMatch.Offset(, 3))
'un-comment for grand total
End Function
It gives the same results now as my macro mentioned
earlier.
I haven't looked too hard for a non-udf solution.


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

The the function doesn't give the full answer is because I'm using your
earlier equation and tacking on this UDF call. The full formula in my grand
total cell is:

=SUM($O$13:$O$25)+SUMPRODUCT(($H$13:$H$25)*(COUNTI F($L$13:$L$25,$E$13:$E$25)=0))+SubTotalMatch($E$13 :$E$25,$L$13:$L$25,$H$13:$H$25)

The SubTotalMatch is intended to just pick up those entries where a match of
A is found in E and F is empty.

"p45cal" wrote:


DocBrown;494694 Wrote:
yea, the code is a bit obscure. *grin* I'm sure I can clean it up a
bunch.

The test data columns correspond to the real data as follows:

A = E = rngSource
B = H = rngSubTotal
E = L = rngMatch
F = O = rngMatch.Offset( ,3)

I probably should pass in that 4th column, or a multi-column range.
Yes,
they were intended to be single columns. As you see, the real data has
other
columns between the columns I want to reference.

John

"p45cal" wrote:


DocBrown;493716 Wrote:
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


DocBrown,
could you clarify the relationship between your original
"Tbl1 A & B, Tbl2 E & F"
and the ranges
ngSource, rngMatch, rngSubTotal
in the code above?
Also should they be single columns?
I'm a bit puzzled with the 3 in:
And cCell.Offset(0, 3).Value


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: 'The Code Cage Forums - View Profile: p45cal'
(http://www.thecodecage.com/forumz/member.php?userid=558)
View this thread: 'Sumproduct forumla for complex sum. - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=134340)



DocBrown,
I tried your function on the original data but I think it gives wrong
results; I stepped through it and (if I've got the ranges correct)
couldn't find
a) where the code added the $75
b) where the code added $50 for the 110.
Below, I've tweaked your function and tacked '2' onto the end of its
name:
Function SubTotalMatch2(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
Dim srcValue
Total = 0

If rngSource.Count < rngMatch.Count Or rngSource.Count <
rngSubTotal.Count Then
SubTotalMatch2 = 0
Exit Function
End If

For cellIndex = 1 To rngSource.Count
srcStr = rngSource.Cells(cellIndex).Value
srcValue = rngSubTotal.Cells(cellIndex).Value
If srcStr = "" Then
Total = Total + srcValue
Else
Set cCell = rngMatch.Find(srcStr, LookIn:=xlValues)
If cCell Is Nothing Then
Total = Total + srcValue
Else
If cCell.Offset(0, 3).Value = "" Then
Total = Total + srcValue
End If
End If
End If
Next cellIndex

SubTotalMatch2 = Total ' + Application.Sum(rngMatch.Offset(, 3))
'un-comment for grand total
End Function
It gives the same results now as my macro mentioned
earlier.
I haven't looked too hard for a non-udf solution.


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

Yes, your function would pick up all of the correct sum. Since it looks like
I need to use a UDF, it would be better to do as you did and do it all in the
UDF.

Thanks
John

"p45cal" wrote:


DocBrown;494694 Wrote:
yea, the code is a bit obscure. *grin* I'm sure I can clean it up a
bunch.

The test data columns correspond to the real data as follows:

A = E = rngSource
B = H = rngSubTotal
E = L = rngMatch
F = O = rngMatch.Offset( ,3)

I probably should pass in that 4th column, or a multi-column range.
Yes,
they were intended to be single columns. As you see, the real data has
other
columns between the columns I want to reference.

John

"p45cal" wrote:


DocBrown;493716 Wrote:
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


DocBrown,
could you clarify the relationship between your original
"Tbl1 A & B, Tbl2 E & F"
and the ranges
ngSource, rngMatch, rngSubTotal
in the code above?
Also should they be single columns?
I'm a bit puzzled with the 3 in:
And cCell.Offset(0, 3).Value


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: 'The Code Cage Forums - View Profile: p45cal'
(http://www.thecodecage.com/forumz/member.php?userid=558)
View this thread: 'Sumproduct forumla for complex sum. - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=134340)



DocBrown,
I tried your function on the original data but I think it gives wrong
results; I stepped through it and (if I've got the ranges correct)
couldn't find
a) where the code added the $75
b) where the code added $50 for the 110.
Below, I've tweaked your function and tacked '2' onto the end of its
name:
Function SubTotalMatch2(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
Dim srcValue
Total = 0

If rngSource.Count < rngMatch.Count Or rngSource.Count <
rngSubTotal.Count Then
SubTotalMatch2 = 0
Exit Function
End If

For cellIndex = 1 To rngSource.Count
srcStr = rngSource.Cells(cellIndex).Value
srcValue = rngSubTotal.Cells(cellIndex).Value
If srcStr = "" Then
Total = Total + srcValue
Else
Set cCell = rngMatch.Find(srcStr, LookIn:=xlValues)
If cCell Is Nothing Then
Total = Total + srcValue
Else
If cCell.Offset(0, 3).Value = "" Then
Total = Total + srcValue
End If
End If
End If
Next cellIndex

SubTotalMatch2 = Total ' + Application.Sum(rngMatch.Offset(, 3))
'un-comment for grand total
End Function
It gives the same results now as my macro mentioned
earlier.
I haven't looked too hard for a non-udf solution.


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

I think I'm between a rock and a hard place. On one hand a UDF allows me to
calculate the sum I want. But on the other, a UDF on the WS causes another
macro called from Worksheet_change to fail with Error 1004.

See my other thread called 'UDF interaction with other macro causes Error
1004'

There's another feature that I want to add that also requires a UDF. So now
what?

Any ideas?

"p45cal" wrote:


DocBrown;494694 Wrote:
yea, the code is a bit obscure. *grin* I'm sure I can clean it up a
bunch.

The test data columns correspond to the real data as follows:

A = E = rngSource
B = H = rngSubTotal
E = L = rngMatch
F = O = rngMatch.Offset( ,3)

I probably should pass in that 4th column, or a multi-column range.
Yes,
they were intended to be single columns. As you see, the real data has
other
columns between the columns I want to reference.

John

"p45cal" wrote:


DocBrown;493716 Wrote:
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


DocBrown,
could you clarify the relationship between your original
"Tbl1 A & B, Tbl2 E & F"
and the ranges
ngSource, rngMatch, rngSubTotal
in the code above?
Also should they be single columns?
I'm a bit puzzled with the 3 in:
And cCell.Offset(0, 3).Value


--
p45cal

*p45cal*

------------------------------------------------------------------------
p45cal's Profile: 'The Code Cage Forums - View Profile: p45cal'
(http://www.thecodecage.com/forumz/member.php?userid=558)
View this thread: 'Sumproduct forumla for complex sum. - The Code
Cage Forums'
(http://www.thecodecage.com/forumz/sh...d.php?t=134340)



DocBrown,
I tried your function on the original data but I think it gives wrong
results; I stepped through it and (if I've got the ranges correct)
couldn't find
a) where the code added the $75
b) where the code added $50 for the 110.
Below, I've tweaked your function and tacked '2' onto the end of its
name:
Function SubTotalMatch2(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
Dim srcValue
Total = 0

If rngSource.Count < rngMatch.Count Or rngSource.Count <
rngSubTotal.Count Then
SubTotalMatch2 = 0
Exit Function
End If

For cellIndex = 1 To rngSource.Count
srcStr = rngSource.Cells(cellIndex).Value
srcValue = rngSubTotal.Cells(cellIndex).Value
If srcStr = "" Then
Total = Total + srcValue
Else
Set cCell = rngMatch.Find(srcStr, LookIn:=xlValues)
If cCell Is Nothing Then
Total = Total + srcValue
Else
If cCell.Offset(0, 3).Value = "" Then
Total = Total + srcValue
End If
End If
End If
Next cellIndex

SubTotalMatch2 = Total ' + Application.Sum(rngMatch.Offset(, 3))
'un-comment for grand total
End Function
It gives the same results now as my macro mentioned
earlier.
I haven't looked too hard for a non-udf solution.


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