count

[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

 

[Don Guillett]

Your sample layout or try to count bob for reason 2
=sumproduct((a2:a22="bob")*(b2:b22=2))

 

[Ron Coderre]

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]

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

 

[PR Kid]

Thank you, however I coild not get this formula to work, it just read "o" no
matter what I did

 

[Don Guillett]

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.

 

[PR Kid]

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

 

[Max]

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

---