Help counting unique numbers

[HeRoseInThree]

Hello. I would like to count my data in column F, but only each unique entry.
If I have multiple examples of a particular number, I need to exclude those.
Thanks in advance

 

[etaf]

Count unique values in a range with COUNTIF

To count the number of unique values in a range of cells, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in F6 is: =SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14)) In Dynamic Excel, you can use a simpler and faster formula based on UNIQUE.

exceljet.net

=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))

Book5
	A	B	C
1	1		4
2	2
3	3
4	4
5	2
6	3
7	4
Sheet1

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))

 

[mumps]

Try this formula. Change the range to suit. =SUMPRODUCT(1/COUNTIF(F1:F12,F1:F12))

 

[HeRoseInThree]

Count unique values in a range with COUNTIF

To count the number of unique values in a range of cells, you can use a formula based on the COUNTIF and SUMPRODUCT functions. In the example shown, the formula in F6 is: =SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14)) In Dynamic Excel, you can use a simpler and faster formula based on UNIQUE.

exceljet.net

=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))

Book5
	A	B	C
1	1		4
2	2
3	3
4	4
5	2
6	3
7	4
Sheet1

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))

The answer schoud be 10, right? 1, 2, 3 & 4 once = 10. Ignoring the duplicate 2, 3 & 4.

 

[etaf]

sorry I read
"I would like to count my data in column F,"
=SUMPRODUCT(1/COUNTIF(A:A,A:A&""),A:A)

 

[HeRoseInThree]

Wrong thread. 4 unique entries. I'm tired! THANKS!

 

[HeRoseInThree]

sorry I read
"I would like to count my data in column F,"
=SUMPRODUCT(1/COUNTIF(A:A,A:A&""),A:A)

Gonna hit the coffee pot 1 more time today!

 

[etaf]

to count the unique entries
=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))
in my example would result in 4

to sum the unique entries
=SUMPRODUCT(1/COUNTIF(A:A,A:A&""),A:A)
in my example would result in 10
or
=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""),A:A)

Book1
	A	B	C	D	E
1	1
2	2
3	1		UNIQUE Values
4	2		Count	5
5	3		Sum	17
6	4
7	1
8	2
9	3
10	7
Sheet1

Cell Formulas
Range	Formula
D4	D4	=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""))
D5	D5	=SUMPRODUCT((A:A<>"")/COUNTIF(A:A,A:A&""),A:A)

 

[AhoyNC]

It would help to know what version of Excel you are using. If you are using Excel 365 then you could use the UNIQUE function.

Book1
	F	G	H	I
1	1
2	2		4	Count
3	3		10	Sum
4	4
5	2
6	3
7	4
Sheet1

Cell Formulas
Range	Formula
H2	H2	=COUNT(UNIQUE(F:F))
H3	H3	=SUM(UNIQUE(F:F))