PDA

View Full Version : Solved: Sum the distinct values under a specific criteria



YasserKhalil
06-15-2010, 04:54 AM
Hi everybody
I have values in column B which can be repeated
and in column A I have names such as YY
I want to sum the values in column B under the criteria (YY) on condition that the values are distinct
DD 15
YY 20
YY 20
DD 50
YY 10

I want the result to be 30

Bob Phillips
06-15-2010, 05:16 AM
Try

=SUM((FREQUENCY(IF(A1:A5="YY",MATCH(B1:B5,B1:B5,0)),ROW(INDIRECT("1:"&ROWS(B1:B5))))>0)*(B1:B6))

YasserKhalil
06-15-2010, 05:37 AM
Good solution Mr xld
but I don't array formula
Is there any solution without array formula
such as UDF Function or somewhat?

Bob Phillips
06-15-2010, 06:12 AM
Function SumUnique(TestRange As Range, TestValue, SumRange As Range)
Dim Results As Variant
Dim NextItem As Long
Dim i As Long


ReDim Results(1 To TestRange.Rows.Count)
For i = 1 To TestRange.Rows.Count

If TestRange.Cells(i, 1).Value2 = TestValue Then

If IsError(Application.Match(SumRange.Cells(i, 1).Value2, Results, 0)) Then

NextItem = NextItem + 1
Results(i) = SumRange.Cells(i, 1).Value2
End If
End If
Next i

SumUnique = Application.Sum(Results)
End Function


Use like

=SumUnique(A1:A5,"YY",B1:B5)

YasserKhalil
06-15-2010, 06:30 AM
Excellent Mr xld
I like that solution very much
You are great person

Last request
I know that I'm greedy but you are generous

My request to build a function like that but to count the distinct values depending on the same criteria
I want the result to be 2 ::I mean the distinct values based on criteria "YY"
Thanks again for your help

JKwan
06-15-2010, 06:58 AM
This variable holds the Unique occurance

NextItem = NextItem + 1

Bob Phillips
06-15-2010, 07:04 AM
Function SumOrCountUnique(TestRange As Range, TestValue, NumberRange As Range, Optional SumData As Boolean = False)
Dim Results As Variant
Dim NextItem As Long
Dim i As Long

ReDim Results(1 To TestRange.Rows.Count)
For i = 1 To TestRange.Rows.Count

If TestRange.Cells(i, 1).Value2 = TestValue Then

If IsError(Application.Match(NumberRange.Cells(i, 1).Value2, Results, 0)) Then

NextItem = NextItem + 1
Results(i) = NumberRange.Cells(i, 1).Value2
End If
End If
Next i

SumOrCountUnique = IIf(SumData, Application.Sum(Results), NextItem)
End Function

YasserKhalil
06-17-2010, 11:33 PM
Mr. xld
I'm admired of you and of this forum
I can't believe it's possible to do so
Thank you very very much
I hope to be your friend. It's a great honour for me

YasserKhalil
06-18-2010, 10:09 AM
What about the maximum value based on a criteria
for example the max for YY is 20
and DD is 50
Is there a function which can do this??