I cannot find where you showed us how/where "permanent", "contract"
etc. are determined/located
with 2009 in A1 and presuming years are in F column

=sumproduct((b2:b22="yes")*(c2:c22="current")*(f2: f22=A1))

would give total current employees with professional qual in the year
2009

adjust yr ranges to suit


On 13 Maj, 17:14, Lal wrote:
Thanks for this. Â*

Please can you also tell me how I might be able to do the following- I also
have a column telling me when the employee joined and if they have left on
what date they left, as well as the previous data. Â*

I would like to be able to show which clients were still current during 2008
(and then each year through to 1999 (under the following catergories-total;
permanent, contract; with professional qualifications; without professional
qualifications)

Â* Â* Â* Â* 2009 Â* Â*2008 Â* Â*2007 Â* Â*2006 Â* Â*2005 Â* Â*2004 Â* Â*2003 Â* Â*2002 Â* Â*2001 Â* Â*2000 Â* Â*1999
Total Permenant Employees Â* Â* Â* 149 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
Total Contract Employees Â* Â* Â* Â*20 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
Total Employees 169 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
Total with qualifications Â* Â* Â* 37 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
Total without qualifications Â* Â*132 Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*
Check Â* TRUE Â* Â*TRUE Â* Â*TRUE Â* Â*TRUE Â* Â*TRUE Â* Â*TRUE Â* Â*TRUE Â* Â*TRUE Â* Â*TRUE Â* Â*TRUE Â* Â*TRUE

I hope this makes sense and that someone can help



"Don Guillett" wrote:

a
=sumproduct((a2:a22="whichqual")*(b2:b22="current" ))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Lal" wrote in message
...
Help- I am looking at some employee information.

Firstly I have a column that shows if the employee has a professional
qualification the second column has whether they are current or have left
the
company.

a) How can I count the number of employees with professional
qualifications
that are current employees?

b) The third column has their length at the company- can someone tell me
how
I can average the time that they have been at the company if they have a
qualification and are current?

It feels like I should be able to do this but for some reason I am
struggling..

eg

A Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* B Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* C
Â* D

Bob Â* Â* Â* Â* Â* Â* Â* Â* Â* Yes Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Current Â* Â* Â* Â* Â* Â* Â* Â*3.5
Jenny Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Current
2.7
Sandy Â* Â* Â* Â* Â* Â* Â* Yes Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Left
6.3
Thomas Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Current Â* Â* Â* Â* Â* Â* Â* Â*5.9
Linda Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Current
3.3

Any help you can give me would be really appreciated

Lal- Ukryj cytowany tekst -

- Pokaż cytowany tekst -