Conditional Distinct Value Sum

[Koby.Fenton]

Hi All, I'm hoping someone can help me with a conditional distinct value sum, I've detailed below an example of what I'm trying to accomplish.


I need to sum balances for distinct Account Identifiers where the sum of the distinct Account Identifiers balances are above a certain threshold. For the below sample data set, the formula would sum the balances for distinct Acct Identifiers that had a sum greater than 10. So for the data below the value returned by the formula would be 54. It would sum the balances for SSN1 & SSN2 because their sums exceeded 10, 35 & 19 respectively but would exclude SSN3 from the sum because it only summed to 7.

Acct Identifier Balance
SSN1 5
SSN1 2
SSN1 8
SSN2 9
SSN1 10
SSN2 9
SSN2 1
SSN1 10
SSN3 4
SSN3 3

Any help would be greatly appreciated!

 

[njimack]

Welcome to the board.

If you're happy using a helper column, try the following...
- enter the following into C2 and copy down as far as required: =IF(COUNTIF(A$1:A1,A2),0,SUMIF($A$2:$A$11,A2,$B$2:$B$11))
- in any empty cell, enter the following: =SUMIF(C2:C11,">10",C2:C11)

(note: you'll need to change the references to row 11 if you have more rows of data)

 

[bryonwoods30]

Do text to columns so that you are SSN1 values are in column A and your balances are in column b.
in c1 put SSN1, c2 put SSN2, c3 put SSN3
put your threshold number "10" in a cell.. i chose e2
then in d1, use the formula
=IF(SUMIF(A:A,C1,B:B)>$E$2,SUMIF(A:A,C1,B:B),"")
d2, use the formula
=IF(SUMIF(A:A,C2,B:B)>$E$2,SUMIF(A:A,C2,B:B),"")
d3, use the formula
=IF(SUMIF(A:A,C3,B:B)>$E$2,SUMIF(A:A,C3,B:B),"")
then just do an autosum in d4 of d1,d2,d3.

After that all you have to do is change your number in e2

 

[Koby.Fenton]

Thanks for the quick replies guys, maybe I should have been more clear.

The real data has legitimate social security numbers for customers in one column and balances in another column. I have over 10k rows of data and the permutations of SSNs is not predictable. I'm pretty sure I'm going to have to use an array formula of some kind to accomplish what I want I'm just not sure how to get the conditional part of the formula into an array.

As for the helper column idea, I've thought of possibly doing that but wanted to avoid messing with the raw data layout in the spreadsheet if at all possible. It just creates possibilities for people to screw up the spreadsheet when I'm not the one running it.

--Koby

 

[njimack]

Are you happy using VBA? If so, paste the following function into a new module in your workbook:

Function SSN(Accounts As Range, Balances As Range, Limit As Long)
Dim r As Range
Dim LR As Long
Dim SSN_Sum As Long
Application.Volatile
Application.EnableEvents = False
LR = Accounts.Count + 1
For Each r In Accounts
    If WorksheetFunction.CountIf(Range(Cells(r.Row + 1, 1), Cells(LR, 1)), r) = 0 Then
        SSN_Sum = WorksheetFunction.SumIf(Accounts, r, Balances)
    Else: SSN_Sum = 0
    End If
    SSN = SSN + SSN_Sum
Next r
        
Application.EnableEvents = True

End Function

With the sample of data you posted, your formula would be as follows:
=SSN(A2:A11,B2:B11,10)

 

[Koby.Fenton]

I tried your macro and it doesn't seem to work properly. I can't follow all of the code logic but my question is where in the code are you applying the condition that the sum be over X (for the test data x=10)?

I ran the code on the test data submitted and I'm getting 246 not 54.

--Koby

 

[njimack]

10 is the 3rd argument of my function (called Limit)

Excel Workbook
	A	B	C	D
1	Acct	Balance		54
2	SSN1	5
3	SSN1	2
4	SSN1	8
5	SSN2	9
6	SSN1	10
7	SSN2	9
8	SSN2	1
9	SSN1	10
10	SSN3	4
11	SSN3	3
Sheet1

 

[Koby.Fenton]

Retried the code in a blank workbook and it worked, something screwy is going on in the workbook with the "production" data. I'm going to start over from scratch and see if I can get it to work. I appreciate the help.

--Koby

 

[Koby.Fenton]

The row the data is in seems to alter the result of the macro. If I put the data in A2:A11 & B2:B11 the macro works as expected, however if I move the data to different rows I get varying results.

 

[njimack]

The row the data is in seems to alter the result of the macro. If I put the data in A2:A11 & B2:B11 the macro works as expected, however if I move the data to different rows I get varying results.

Well obviously you need to amend the function so that it refers to the correct range.

If the size of your data is changeable, consider using dynamic ranges - see http://www.contextures.com/xlNames01.html#Dynamic