Getting a total in a pivot table, excluding the duplicates.

J

J.W. Aldridge

I had following formula to give me the count of the single instances
of a repeated name.
I am trying to upgrade my report and use a pivot table, but wonder how
to use this formula/or a simpler way to count only the unique instance
in a column/header.

Any advice?

=SUM(IF(FREQUENCY(IF(LEN(B:B)>0,MATCH(B:B,B:B,0),""),
IF(LEN(B:B)>0,MATCH(B:B,B:B,0),""))>0,1))


Example:
Names in column A.
Codes in column B.
Some of the codes in column B are repeated.
In my pivot, I want to roll up how many codes (excluding the
duplicates) each person had.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code copies between key word is in reverse order 5
modified pivot table using a macro 2
I'm trying to do a "count" function at the end of a dynamic pivot table 0
VBA Pivot Table: Determining item from one field based on anotherfield item 0
Error trapping not working 1
Count Number of Unique Cells That Don't Have Certain Characters 0
Count unique values in one column if values in corresponding columnare null? 1
Pivot Table categories 4

Top