Sumproduct forumla for complex sum.

[DocBrown]

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

 

[Peter T]

If I follow,

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

Regards,
Peter T

 

[p45cal]

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

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

 

[DocBrown]

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]

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

 

[DocBrown]

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

 

[Peter T]

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

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]

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 you
sample
data.

Regards,
Peter TYes, Peter, it gives the same result for his example. But furthe

testing yields some odd results; If the ranges in your formula ar
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 i
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 th
value empty..

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

 

[Peter T]

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]

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

 

[p45cal]

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.

 

[DocBrown]

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

 

[Peter T]

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

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]

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

 

[DocBrown]

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

 

[Bernie Deitrick]

When I try your code with your values (moving A to E, B to H, E to L, and F to O) and using

=SubTotalMatch(E1:E5,L1:L5,H1:H5)

I get 121.

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

But on the above, why not just use

=SUM(F1:F5)+SUMPRODUCT((E1:E5<>"")*(F1:F5="")*B1:B5)+SUMIF(A1:A5,"",B1:B5)

which returns 946 for me.


HTH,
Bernie
MS Excel MVP

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

 

[DocBrown]

I don't think I'm clear on what the rules are. The formula you presented has
characterisitic that I think will not return the correct value with different
data. If you reverse A1:B1 and A2:B2 you might see the results.

The first reason is that the rows in Tbl1 in col A & B are independent to
Tbl2 E & F.

The desired rules are:
- The user can enter values (from a dropdown list only) in E. cells in A are
locked and the values are generated from other columns in Tbl1 that are not
shown.
- If an value is entered in Ax and that value does not exist in E, the value
is added at the end of E. Tbl2 is then sorted.
-The user adds values in B and F at will. Other checks verify that the sum
of matching items in A do not exceed E. Ex: if B2+B5 > F1. highlight B2 &
B5.(done with conditional formatting.)

- Add all items in E1:E15
- if Ax is empty, add the value in Bx.
- Find the value of Ax in Tbl2 col Ey and if Col Fy is empty add Bx.

Here's some new data. I hope this helps.

A B C D E F
1 01-120 $121.00 01-100 $300.00
2 01-100 $100.00 01-120
3 01-150 $335.00 01-130 $200.00
4 $75.00 01-140 $250.00
5 01-100 $25.00 01-150
6 $65.00
7 01-140 $218.00

Sum = (F1 + F3 + F4) + (B4 + B6) + (B1+B3)
= (300 + 200 + 250) + (75 + 65) + (121 + 335) = 1346

Again, Thanks to all of you for the time you've invested in this.

John

When I try your code with your values (moving A to E, B to H, E to L, and F to O) and using

=SubTotalMatch(E1:E5,L1:L5,H1:H5)

I get 121.

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

But on the above, why not just use

=SUM(F1:F5)+SUMPRODUCT((E1:E5<>"")*(F1:F5="")*B1:B5)+SUMIF(A1:A5,"",B1:B5)

which returns 946 for me.


HTH,
Bernie
MS Excel MVP

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]

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


Cage Forums'
(http://www.thecodecage.com/forumz/showthread.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.

 

[DocBrown]

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)*(COUNTIF($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.

 

[DocBrown]

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