summary report with duplicates
 

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!

summary report with duplicates
 

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

"Jasmine" wrote in message
...
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!

summary report with duplicates
 

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.

"Biff" wrote:

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

"Jasmine" wrote in message
...
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!

summary report with duplicates
 

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

"Jasmine" wrote in message
...
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.

"Biff" wrote:

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

"Jasmine" wrote in message
...
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!