count the no of specific position

  • Thread starter Thread starter aboiy
  • Start date Start date
A

aboiy

Hi,

Our company have more than 900 workers with approx. 35
different position. I need to count the total no. of each
respective position that attended the seminars.

Using "sort" will only arrange the position in
alphabetical order and "transpose" will only give me the
distinct position but not the total of each position.

A B C D
Name Position Dept. Total
Electrn Eng
Secretary Admin
Plumber Eng
Waiter F&B
Controller Purch
PO Analyst Purch
Elect.Sup Eng
Electrn Eng
Waiter F&B
Secretary Fin

Can we make a formula to reflect the total in column D
based upon column B & C.

Thank you.

aboiy
 
Hi aboiy!

Try:

=SUMPRODUCT(($B$2:$B$11="Electm")*(($C$2:$C$11="Eng")))

Probably best to set up a table of the various Positions and
Departments and then to refer to positions and Departments by cell
references in that table.

Better still is to try using a Pivot Table approach.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
aboiy wrote,

"Our company have more than 900 workers with approx. 35
different position. I need to count the total no. of each
respective position that attended the seminars."
Click to expand...

aboiy,

Once again I believe that you will find Pivot Tables the weapon of
choice.

Use formulas if you wish, but you are missing the power of this
inbuilt data analysis tool.

David
 
Hi David!

Certainly agreed here. On the data given we have 8 different positions
and 5 departments. The full data is likely to have more of both.

Pivot Tables are just made for this.

OP should refer to Debra's Introduction to Pivot Tables for the
basics.

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top