Apply Count function for a particular number repeated more than once.

[VDS]

Dear Sir,

I have prepared the list of numbers as per attachment in which the numbers are repeated

Column A gives the List of so many GRN Numbers, In Column B, I applied if <> for separation and Count to get the total no of GRN.

Here what I want the result 7 should be displayed in Column A with a single Formula without column B. (i.e If and Count can be clubbed). How it can done. Help is requested.

VDS

 

[Lymm]

Hi, try this formula, it worked for me
=SUMPRODUCT(1/COUNTIF(A$2:$A20,A2:A20))

 

[Mike H..]

Dear Sir,

I have prepared the list of numbers as per attachment in which the numbers are repeated

Column A gives the List of so many GRN Numbers, In Column B, I applied if <> for separation and Count to get the total no of GRN.

Here what I want the result 7 should be displayed in Column A with a single Formula without column B. (i.e If and Count can be clubbed). How it can done. Help is requested.

VDS

Hi,

A bit longer but will not throw an error with blanks.

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

 

[Faseeh]

This is another: =SUM((FREQUENCY(A2:A20,A2:A20)<>0)*1)

 

[VDS]

@Dear Fasheesh / Mike,

Nice reply. Thanks a lot


VDS