# Help counting duplicate values

#### Mchgh9

##### New Member
Hi,

I have a large amount of data that contains a lot of duplicate records. The records can have different states but I just want to count the number of times a specific cell appears in the list of data, without removing duplicates.

 Tag? Insp. Date Insp. Time Insp. Inspection Type Description Inspection Outcome Insp. OD Promoter Reference N 18/12/2003 15:01 RTN Routine Category B PASSED LA001 MERS00069694 N 07/06/2005 11:55 D/1 Defect Joint Site Visit Category C FAIL-LOW RISK GT001 MERS00075412 N 19/08/2005 00:00 D/1 Defect Joint Site Visit Category C FAIL-LOW RISK GT001 CC59463 N 13/11/2006 15:03 D/2 Defect Follow Up Category C FAIL-LOW RISK PN001 WR125212 N 27/11/2006 16:00 D/1 Defect Joint Site Visit Category C FAIL-LOW RISK PN001 WR125212 N 03/01/2007 09:05 D/1 Defect Joint Site Visit Category C FAIL-LOW RISK PN001 WR125212 N 30/01/2007 13:04 D/2 Defect Follow Up Category C FAIL-LOW RISK PN001 WR125212 N 23/03/2007 11:01 D/1 Defect Joint Site Visit Category C FAIL-LOW RISK PN001 WR125212 N 20/04/2007 09:00 D/2 Defect Follow Up Category C FAIL-LOW RISK PN001 WR125212 N 09/05/2007 12:00 D/1 Defect Joint Site Visit Category C FAIL-LOW RISK PN001 WR125212

<TBODY>
</TBODY><COLGROUP><COL span=7><COL></COLGROUP>

This is a small sample of the data. As you can see in the works reference WR125212 appears a number of times. There are approx 20,000 rows with various different references in this data.

Is there any way I can count the number of unique reference numbers, possibly using a pivot table or a formula?

Thanks

Dave

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### TheCman81

##### Well-known Member
You can use something like this

=SUM(IF(FREQUENCY(MATCH(\$G\$2:\$G\$17,\$G\$2:\$G\$17,0),ROW(\$G\$2:\$G\$17)-1),1,0))

#### EstherBezzy

##### New Member
Hi Dave,

You can also do this with a pivot table. Put the "Promoter Reference" field in both the Values and Row areas. Since it's text, the default calculation in the pivot will be Count. Should look omething like the table below.

 Row Labels Count of Promoter Reference CC59463 1 MERS00069694 1 MERS00075412 1 WR125212 7 Grand Total 10

<tbody>
</tbody>

Now, if only I could figure out how to post an image here to give the actual illustration....

Best regards,
Esther