count

  • Thread starter Thread starter PR Kid
  • Start date Start date
P

PR Kid

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
 
Your sample layout or try to count bob for reason 2
=sumproduct((a2:a22="bob")*(b2:b22=2))
 
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)
 
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
 
Thank you, however I coild not get this formula to work, it just read "o" no
matter what I did
 
As REQUESTED. What is your layout and examples. How can you possibly expect
us to help without some detail. There are NO mind readers here.
 
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
 
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

---
 
Back
Top