ExcelBanter - Counting Data

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Counting Data (https://www.excelbanter.com/new-users-excel/50262-counting-data.html)

dave

Counting Data

I have a list of data that can have the same value. How can i count the
number of similarities and get it to display the result in the next row. For
example:-

BOTTLE_CHK-2707
BOTTLE_CHK-2707
CALSOLUT-0563
DEF-ESTATE-2088
DEF-ESTATE-2088
EDEN_DC-2682
ELS_LTD-2450
ELS_LTD-2450
ESSEXCRP
ESSEXCRP
ESSEXCRP
HARDING_EN-2558

Thanks in advance

Bob Phillips

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

change the A20 to wherever your data ends.

--
HTH

Bob Phillips

"dave" wrote in message
...
I have a list of data that can have the same value. How can i count the
number of similarities and get it to display the result in the next row.
For
example:-

BOTTLE_CHK-2707
BOTTLE_CHK-2707
CALSOLUT-0563
DEF-ESTATE-2088
DEF-ESTATE-2088
EDEN_DC-2682
ELS_LTD-2450
ELS_LTD-2450
ESSEXCRP
ESSEXCRP
ESSEXCRP
HARDING_EN-2558

Thanks in advance

All times are GMT +1. The time now is 05:26 AM.