Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Spreadsheet Summary Report | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Summary Report (OLAP kind) | Charts and Charting in Excel | |||
How to create summary of expense report? | Excel Worksheet Functions | |||
Can I create an order summary report in Excel? | Excel Worksheet Functions |