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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[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

 

[DocBrown]

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?

 

[Bernie Deitrick]

Doc,

The LOOKUP or INDEX double array requirement prevents a single cell formula,
but you can use a column of helpers.

In G1, enter

=IF(AND(E1<>"",F1=""),INDEX($B$1:$B$7,MATCH(E1,$A$1:$A$7,FALSE)),"")

and copy down to G7.

Then use

=SUM(F1:G7)+SUMIF(A1:A7,"",B1:B7)

to give 1,346.

HTH,
Bernie
MS Excel MVP

 

[DocBrown]

Hi Bernie,

Thanks for your input. Using the helper cells is a good idea. It makes the
solution much simpler. Because of my requirements. I needed to use a
different formula. The items in A can appear multiple times. But using MATCH
only picks up the first one in A.

Here's what I did.
In G1:G7:
=IF(AND(E1<>"",F1=""),SUMIF($A$1:$A$7,E1,$B$7:$B$7),"")

In the total sum cell I used your function.

Thanks for the new approach.
John