Count the occurance of a duplicate in a column using structured references

[sspatriots]

Hi,

I have a table with the name "JobList". I have column that list a boat load of e-mails, and many are duplicates. That column name is "Contact Email Address". To find the actual occurrence number of the duplicates I was trying to do it two different ways. The first column formula (below) will count the number of duplicates for any email address in that column I just mentioned. The formula is as follows:

=COUNTIF([Contact Email Address], [@[Contact Email Address]])

Therefore, I tried this next formula to try and get the occurrence number of the duplicates from that same column:

=COUNTIF(JobList[@[Contact Email Address]:[Contact Email Address]],[@[Contact Email Address]])

However, that just put the number "1" in every cell down that column.

If an e-mail address shows up 5 times down that "Contact Email Address" column, would like the formula result to read "1" in the new column when the initial e-mail address appears in that column from top to bottom, then the second time that same e-mail address appears in that column I would like the formula result to read "2", then "3", then "4" and so on.

I have no clue how to do this using the structured references. Thanks, SS

 

[Cubist]

Try:

=COUNTIF(INDEX([Contact Email Address],1):[@[Contact Email Address]],[@[Contact Email Address]])

 

[awoohaw]

try removing the @ symbol:

       =COUNTIF(JobList[[Contact Email Address]:[Contact Email Address]],[@[Contact Email Address]])

 

[sspatriots]

Thanks for the feedback. The first one work just like I needed it to.