Hello everyone. Attached is a sample table, which should be far easier to understand what I am trying to accomplish. So please try to use that for reference.
What I am trying to do is to use a dataset with Column A "state" and Column B "value" to create a table showing the frequency at which values duplicate in the data. For example, if two distinct values (a-1, a-4) each show up 3 times, then the result would be that the Duplicate Frequency would be 3 and the Duplicates Occur would be 2.
I am able to complete the intermediate step with using the CountIF formula to count the number of duplicates and place that in Column C "Times Value Appears". Then using pivot tables I can list the states, values, and count of times value appears.
When I try to create the Results table, however, I set the Row to times value appears and the Data to count of times value appears. The issue is that when I do this, the count is being done across the identical values instead of treating them as a single group. The resulting totals are actually being multiplied by the times the value appears and not just treating it as a single occurrence.
Many thanks for your help!
Bookmarks