Sum duplicate values only once and with one criteria (formula needed)

[Masha92]

Hello everyone,

I am desperate with this challenge.

1
3
4
3
4
3
6
5
5
2

I need to sum the above column with the below criteria:
1. Sum values only once (as some values will be duplicated)
2. The value must be more than 3.

So I need it as 4+5+6=15

I was able to satisfy the first condition by using sumproduct and countif. But I was not able to fit in the 2nd criteria.

Can you please help?

Thank you in advance!

 

[alansidman]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Column1] > 3),
    #"Calculated Sum" = List.Sum(#"Filtered Rows"[Column1])
in
    #"Calculated Sum"

 

[Masha92]

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Filtered Rows" = Table.SelectRows(#"Removed Duplicates", each [Column1] > 3),
    #"Calculated Sum" = List.Sum(#"Filtered Rows"[Column1])
in
    #"Calculated Sum"

Thanks, but I need excel formula.
I am already using VBA to solve this for the time being. But I can only use an excel formula.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Peter_SSs]

Depending on your version ..

22 11 26.xlsm
	A	B	C
1	1	15	15
2	3
3	4
4	3
5	4
6	3
7	6
8	5
9	5
10	2
Sum

Cell Formulas
Range	Formula
B1	B1	=SUM(UNIQUE(FILTER(A1:A10,A1:A10>3,0)))
C1	C1	=SUMPRODUCT((A1:A10>3)*(MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)-ROW(A1)+1),A1:A10)

 

[Masha92]

Depending on your version ..

22 11 26.xlsm
	A	B	C
1	1	15	15
2	3
3	4
4	3
5	4
6	3
7	6
8	5
9	5
10	2
Sum

Cell Formulas
Range	Formula
B1	B1	=SUM(UNIQUE(FILTER(A1:A10,A1:A10>3,0)))
C1	C1	=SUMPRODUCT((A1:A10>3)*(MATCH(A1:A10,A1:A10,0)=ROW(A1:A10)-ROW(A1)+1),A1:A10)

Works like charm!
Thanks a lot and I will update my account as suggested.

 

[Peter_SSs]

Works like charm!
Thanks a lot ..

You're welcome. Thanks for the follow-up.

.. and I will update my account as suggested

Please don't forget to do that.