Sum Unique Values based on Criteria

[Starrbuckk]

Hi everyone,

I need help summing values that may repeat themselves and have a criteria that may repeat itself also.


[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]Y[/TD]
[/TR]
[TR]
[TD="class: xl66"]a[/TD]
[TD="class: xl66"]10[/TD]
[/TR]
[TR]
[TD="class: xl67"]b[/TD]
[TD="class: xl67"]10[/TD]
[/TR]
[TR]
[TD="class: xl67"]c[/TD]
[TD="class: xl67"]20[/TD]
[/TR]
[TR]
[TD="class: xl68"]a[/TD]
[TD="class: xl68"]20[/TD]
[/TR]
[TR]
[TD="class: xl67"]b[/TD]
[TD="class: xl67"]30[/TD]
[/TR]
[TR]
[TD="class: xl66"]a[/TD]
[TD="class: xl66"]10[/TD]
[/TR]
[TR]
[TD="class: xl67"]c[/TD]
[TD="class: xl67"]40[/TD]
[/TR]
[TR]
[TD="class: xl67"]c[/TD]
[TD="class: xl67"]30[/TD]
[/TR]
[TR]
[TD="class: xl66"]d[/TD]
[TD="class: xl66"]20[/TD]
[/TR]
[TR]
[TD="class: xl68"]d[/TD]
[TD="class: xl68"]50[/TD]
[/TR]
[TR]
[TD="class: xl68"]e[/TD]
[TD="class: xl68"]30[/TD]
[/TR]
[TR]
[TD="class: xl66"]d[/TD]
[TD="class: xl66"]20[/TD]
[/TR]
[TR]
[TD="class: xl66"]e[/TD]
[TD="class: xl66"]10[/TD]
[/TR]
[TR]
[TD="class: xl66"]e[/TD]
[TD="class: xl66"]10[/TD]
[/TR]
</tbody>[/TABLE]


The idea is to sum only unique values based on the specified criteria. Example of desired result:

[TABLE="width: 163"]
<tbody>[TR]
[TD]Condition X[/TD]
[TD]Sum Value Y[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]70[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]

For some reason i can't upload any attachment to the post, but i hope i've explained myself well enough.

 

[MickG]

Try this for results starting "C1"

[COLOR="Navy"]Sub[/COLOR] MG01Feb29
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn.Offset(, 1).Value
    [COLOR="Navy"]Else[/COLOR]
        .Item(Dn.Value) = .Item(Dn.Value) + Dn.Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
Range("C1").Resize(.Count, 2) = Application.Transpose(Array(.Keys, .items))
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Starrbuckk]

But is there a set of formulas i can use?

Thank you.

 

[Aladin Akyurek]

Are the conditions given or must be extracted?

 

[Starrbuckk]

Conditions are extracted from list, then removed duplicates to sum only unique values based on the condition from an "array" i guess?

 

[Aladin Akyurek]

Conditions are extracted from list, then removed duplicates to sum only unique values based on the condition from an "array" i guess?

I was asking whether that unique is known in advance... I guess not. If so:

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td] X[/td][td] Y[/td][td][/td][td]

5​

[/td][td][/td][/tr]
[tr][td]

2​

[/td][td] a[/td][td] 10[/td][td][/td][td]Unique X[/td][td]Total Y[/td][/tr]
[tr][td]

3​

[/td][td] b[/td][td] 10[/td][td][/td][td]a[/td][td]

40​

[/td][/tr]
[tr][td]

4​

[/td][td] c[/td][td] 20[/td][td][/td][td]b[/td][td]

40​

[/td][/tr]
[tr][td]

5​

[/td][td] a[/td][td] 20[/td][td][/td][td]c[/td][td]

90​

[/td][/tr]
[tr][td]

6​

[/td][td] b[/td][td] 30[/td][td][/td][td]d[/td][td]

90​

[/td][/tr]
[tr][td]

7​

[/td][td] a[/td][td] 10[/td][td][/td][td]e[/td][td]

50​

[/td][/tr]
[tr][td]

8​

[/td][td] c[/td][td] 40[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td] c[/td][td] 30[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

10​

[/td][td] d[/td][td] 20[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

11​

[/td][td] d[/td][td] 50[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

12​

[/td][td] e[/td][td] 30[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

13​

[/td][td] d[/td][td] 20[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

14​

[/td][td] e[/td][td] 10[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

15​

[/td][td] e[/td][td] 10[/td][td][/td][td][/td][td][/td][/tr]
[/table]

In D1 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$15=""),MATCH($A$2:$A$15,$A$2:$A$15,0)),ROW($A$2:$A$15)-ROW($A$2)+1),1))

In D3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($D$3:D3)>$D$1,"",INDEX($A$2:$A$15,SMALL(IF(FREQUENCY(IF(1-($A$2:$A$15=""),MATCH($A$2:$A$15,$A$2:$A$15,0)),ROW($A$2:$A$15)-ROW($A$2)+1),ROW($A$2:$A$15)-ROW($A$2)+1),ROWS($D$3:D3))))

In E3 just enter and copy down:

=IF($D3="","",SUMIFS($B$2:$B$15,$A$2:$A$15,$D3))

 

[Starrbuckk]

Sorry i didnt explain myself well. The conditions are given, i have a list (List A) of numbers (conditions) that i copy+paste somewhere else, then remove the duplicate values to create a unique condition list (List B). Only after that i will want to sum the values of those conditions from the previous list (List A) to my new list (List B).
The thing is, if you see my 1st post about the desired result, you will notice your formula "=IF($D3="","",SUMIFS($B$2:$B$15,$A$2:$A$15,$D3))" does not meet the requirements.
Unique value "A" as 3 entries with values: 10;10;20;. The sum of these values should disregard summing repeated numbers "10" more than once and return the result of the sum of only unique values "10;20" which equals 30, as should the rest of the conditions behave the same way for each of their respected values.

Thanks in advance for your help.

 

[Aladin Akyurek]

Post the desired results, not the results of some formula which does not work.

 

[Starrbuckk]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Row/Column[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E (Wanted Results)[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Criteria[/TD]
[TD]Value[/TD]
[TD][/TD]
[TD]Criteria[/TD]
[TD]Sum Value[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]a[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]a[/TD]
[TD]40 (10+30)[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]B[/TD]
[TD]70 (30+40)[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]c[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]c[/TD]
[TD]25 (10+15)[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]a[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]B[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]B[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]a[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]c[/TD]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]a[/TD]
[TD]30[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Highlighted the ones that repeat and should count only once.
Does this help?

 

[Aladin Akyurek]

Right. Thanks for the clarification.

Replace the formula in D3 with the following in the set up I described earlier...

Control+shift+enter, not just enter, and copy down:

=IF($D3="","",SUM(IF(FREQUENCY(IF($A$2:$A$15=D3,MATCH($B$2:$B$15,$B$2:$B$15,0)),ROW($B$2:$B$15)-ROW($B$2)+1),$B$2:$B$15)))