Excel Count Reprated Items Once

[ali zaib]

Your kind support required thanks in advance
Can we count repeated items just once .

Column 1
A
B
C
A
D
C

Required result with count in cell B1 = 4

 

[hiker95]

ali zaib,

Try the following formula in cell B1:

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


Try the following formula in cell C1:

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

 

[admiral100]

Hi,

Another option : =SUM(--(FREQUENCY(A1:A6,A1:A6)>0))

 

[Aladin Akyurek]

Your kind support required thanks in advance
Can we count repeated items just once .

Column 1
A
B
C
A
D
C

Required result with count in cell B1 = 4

A faster option... control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(A2:A7=""),MATCH(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1),1))