count the no of specific position

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="En g")))

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

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

Norman,

I'll apply your tips upon my return to the office.

Thank you.

aboiy


-----Original Message-----
Hi aboiy!

Try:

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

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

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."

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/...pivotstart.htm

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.