Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
count
I have a table that shows all the employees that have been termed. I want to
count how many different codes each manager has used in a given amount of time. ie: bob termed 10 employees using codes 2,3,5,9,12,2,5. what formula can I use to show me: two terms code 2, two term code 5 an so on for a total term of 7 employees Have a great day |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
count
Your sample layout or try to count bob for reason 2
=sumproduct((a2:a22="bob")*(b2:b22=2)) -- Don Guillett Microsoft MVP Excel SalesAid Software "PR Kid" wrote in message ... I have a table that shows all the employees that have been termed. I want to count how many different codes each manager has used in a given amount of time. ie: bob termed 10 employees using codes 2,3,5,9,12,2,5. what formula can I use to show me: two terms code 2, two term code 5 an so on for a total term of 7 employees Have a great day |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
count
With
Col_A containing Employee Col_B containing MgrName Col_C containing TermCode Try this: E2: (MgrName to query for.....eg Bob) Enter this ARRAY FORMULA, committed with CTRL+SHIFT+ENTER (instead of just ENTER) in... F2: =COUNT(1/FREQUENCY(IF(B$2:B$15=E2,C$2:C$15),C$2:C$15)) Adjust range references to suit your situation. Is that something you can work with? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "PR Kid" wrote in message ... I have a table that shows all the employees that have been termed. I want to count how many different codes each manager has used in a given amount of time. ie: bob termed 10 employees using codes 2,3,5,9,12,2,5. what formula can I use to show me: two terms code 2, two term code 5 an so on for a total term of 7 employees Have a great day |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
count
Thank you for your help, however using your formula only counts how many
times a manager's name shows up, what I whanted to do was show how many times the manager's name shows up under each code. ie: bob has a total of 3 terms, two under code 8 and 1 under code 5 -- Have a great day "Don Guillett" wrote: Your sample layout or try to count bob for reason 2 =sumproduct((a2:a22="bob")*(b2:b22=2)) -- Don Guillett Microsoft MVP Excel SalesAid Software "PR Kid" wrote in message ... I have a table that shows all the employees that have been termed. I want to count how many different codes each manager has used in a given amount of time. ie: bob termed 10 employees using codes 2,3,5,9,12,2,5. what formula can I use to show me: two terms code 2, two term code 5 an so on for a total term of 7 employees Have a great day |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
count
Thank you, however I coild not get this formula to work, it just read "o" no
matter what I did -- Have a great day "Don Guillett" wrote: Your sample layout or try to count bob for reason 2 =sumproduct((a2:a22="bob")*(b2:b22=2)) -- Don Guillett Microsoft MVP Excel SalesAid Software "PR Kid" wrote in message ... I have a table that shows all the employees that have been termed. I want to count how many different codes each manager has used in a given amount of time. ie: bob termed 10 employees using codes 2,3,5,9,12,2,5. what formula can I use to show me: two terms code 2, two term code 5 an so on for a total term of 7 employees Have a great day |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
count
Sorry about that. Here it goes
#3 #4 and #5 are the term codes. Under each manager name is the Number of terms for each code. I want to populate these field by reading the information from a master sheet. So that at any time I can see a total tally by manager, by code. The master sheet would have alot of information about the employee, but I only want to show the term stats. at any given time a manager could have any # terms all for a different reason. Asbury Arthurton Abraham Borja = Manager names 3 1 1 1 1 4 1 1 1 5 1 1 -- Have a great day "PR Kid" wrote: I have a table that shows all the employees that have been termed. I want to count how many different codes each manager has used in a given amount of time. ie: bob termed 10 employees using codes 2,3,5,9,12,2,5. what formula can I use to show me: two terms code 2, two term code 5 an so on for a total term of 7 employees Have a great day |
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
count
Here's a quick sample to illustrate one possibility:
http://www.freefilehosting.net/download/3bdb0 Sumproduct_04022008.xls Assume source data in sheet: x, in cols A and B, data from row2 down, like this: Mg Tcode Asbury 3 Arthurton 4 Abraham 3 etc In another sheet, you have the summary table you posted, codes in A2 down, Mg names in B1 across Place in B2, copy across/fill down to populate: =SUMPRODUCT((x!$A$2:$A$10=B$1)*(x!$B$2:$B$10=$A2)) Then switch off display of zeros in the sheet via clicking: Tools Options Uncheck Zero values OK You could also easily create a pivot table on the source data in x to get the desired results -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "PR Kid" wrote: Sorry about that. Here it goes #3 #4 and #5 are the term codes. Under each manager name is the Number of terms for each code. I want to populate these field by reading the information from a master sheet. So that at any time I can see a total tally by manager, by code. The master sheet would have alot of information about the employee, but I only want to show the term stats. at any given time a manager could have any # terms all for a different reason. Asbury Arthurton Abraham Borja = Manager names 3 1 1 1 1 4 1 1 1 5 1 1 -- Have a great day "PR Kid" wrote: I have a table that shows all the employees that have been termed. I want to count how many different codes each manager has used in a given amount of time. ie: bob termed 10 employees using codes 2,3,5,9,12,2,5. what formula can I use to show me: two terms code 2, two term code 5 an so on for a total term of 7 employees Have a great day |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |