summary report with duplicates

J

Jasmine

I have a report that lists the following columns:

Month Port# Name
April 123 Smith
April 123 Smith
April 234 Smith
April 345 Taylor
April 456 Taylor
May 567 Cox

I need a summary report from this that counts for each month and name how
many occurences. However, if the port# field is the same it counts it once.
So for April, Smith would have 2, Taylor would have 2. I tried to use a
PivotTable, but can't figure out how to not get it to count 123 twice. Any
suggestions would be greatly appreciated. Thanks!
 
B

Biff

Hi!

Assume your table is in the range A2:C7

G2 = April
H2 = Smith

Array enterd using the key combination of CTRL,SHIFT,ENTER:

=SUM(N(FREQUENCY(IF((A2:A7=G2)*(C2:C7=H2),MATCH(B2:B7,B2:B7,0)),MATCH(B2:B7,B2:B7,0))>0))

Biff
 
J

Jasmine

My problem lies in that what shows up in the name column could end up being
up to 300 different entries. I don't want to have to define what all those
entires could be. They will change occasionally as we add or get rid of
employees.
 
B

Biff

Use an advanced filter for the unique names and use the copy to another
location option then you just copy the formula for each name.

Biff
 
Top