Sum of values without duplicates

[Yop59]

Hi,

I want to do a sum of values ​​without duplicates. For example, I have an array like this :

Please Login or Register  to view this content.

And I want at the end :

Please Login or Register  to view this content.

I have try with UNIQUE, COUNTIF, SUM, FILTER, .... but I can't do it

Have you an idea ?

Thank you in advance.

(Sorry for my bad english)

Yop59

[FDibbins]

Hi and welcome to the forum

assuming your data is in A2:C2, use this in a helper column (I used D2 down)...
=COUNTIFS($A$2:A2,A2,$C$2:C2,C2)
then use this to sum, based on the helper...
=SUMIFS($B$2:$B$9,$D$2:$D$9,1,$A$2:$A$9,F2)
(I have the choise1 etc in F2:F4)

[AlKey]

Hi Yop59,

Please see attached file.

You may need to replace "," with ";"

[Tony Valko]

You want a unique count, not a sum.

Try this...

Data Range

	A	B
1	Choice1	1
2	Choice1	2
3	Choice1	3
4	Choice1	4
5	Choice2	1
6	Choice2	2
7	Choice2	4
8	Choice3	1
9
10
11	Choice1	4
12	Choice2	3
13	Choice3	1

This array formula** entered in B11 and copied down:

=SUM(IF(FREQUENCY(IF(A$1:A$8=A11,B$1:B$8),B$1:B$8),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[FDibbins]

AlKey, I think you forgot the "TYPE" column?

[AlKey]

AlKey, I think you forgot the "TYPE" column?

Hmm.. result is the same. I looked at what OP's final result is.

[Yop59]

Hi all,

Thank you for your answers !

FDibbins, is it possible to have the formula on one line ?

AlKey and Tony Valko, it's not a COUNT but a SUM taking into consideration the column "Type".

For example, the Choice1 = 4 because there are 'Choice1 1 one' (without duplicates) and 'Choice1 3 two' (value 1 + value 3 = 4) and not because I have four "Choice1".

An idea ?

Thanks

Yop59

[Tony Valko]

OK, now I understand.

Try this...

Data Range

	A	B	C
1	Choice1	1	one
2	Choice1	2	one
3	Choice1	3	two
4	Choice1	4	one
5	Choice2	1	one
6	Choice2	2	two
7	Choice2	4	two
8	Choice3	1	three
9
10
11	Choice1	4
12	Choice2	3
13	Choice3	1

This array formula** entered in B11 and copied down:

=SUM(IF(FREQUENCY(IF(A$1:A$8=A11,IF(MATCH(A$1:A$8&C$1:C$8,A$1:A$8&C$1:C$8,0)=ROW(A$1:A$8)-ROW(A$1)+1,ROW(A$1:A$8))),ROW(A$1:A$8)),B$1:B$8))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Yop59]

Tony Valko, thank you for your answer but I have an error with your formula (with Google Spreadsheet or LibreOffice Calc).

Have you an idea why ?

I have change the "," with ";" or the "A$80" with "A$8".

(Sorry for all these questions....)

[AlKey]

Deleted post.

[Tony Valko]

Tony Valko, thank you for your answer but I have an error with your formula (with Google Spreadsheet or LibreOffice Calc).

Have you an idea why ?

I don't know anything about those applications.

This is an Excel forum!

[FDibbins]

No sorry, I'm not sure that they can be combined, but you can always hide the helper column

[Yop59]

OK, now I understand.

Try this...

Data Range

	A	B	C
1	Choice1	1	one
2	Choice1	2	one
3	Choice1	3	two
4	Choice1	4	one
5	Choice2	1	one
6	Choice2	2	two
7	Choice2	4	two
8	Choice3	1	three
9
10
11	Choice1	4
12	Choice2	3
13	Choice3	1

This array formula** entered in B11 and copied down:

=SUM(IF(FREQUENCY(IF(A$1:A$8=A11,IF(MATCH(A$1:A$8&C$1:C$8,A$1:A$8&C$1:C$8,0)=ROW(A$1:A$8)-ROW(A$1)+1,ROW(A$1:A$8))),ROW(A$1:A$8)),B$1:B$8))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Thank you for your answer Tony Valko (and the others also of course), it works perfectly !!!!

PS : Sorry for the response time

Yop59

[Tony Valko]

You're welcome. Thanks for the feedback!