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

=AVERAGE(IF((B1:B5="Yes")*(C1:C5="Current"),D1:D5) )

CTRL+SHIFT+ENTER this formula as it is an array-formula



On 13 Maj, 11:41, Lal wrote:
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

And for the first question?

"Jarek Kujawa" wrote:

=AVERAGE(IF((B1:B5="Yes")*(C1:C5="Current"),D1:D5) )

CTRL+SHIFT+ENTER this formula as it is an array-formula



On 13 Maj, 11:41, Lal wrote:
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

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

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

for 1st you may use Don's formula

or:

=SUM(IF((B1:B5="Yes")*(C1:C5="Current"),1))
(hope I didn't mess up with ranges)

CTRL+SHIFT+ENTER this formula as it is an array-formula



On 13 Maj, 12:48, Lal wrote:
And for the first question?



"Jarek Kujawa" wrote:
=AVERAGE(IF((B1:B5="Yes")*(C1:C5="Current"),D1:D5) )

CTRL+SHIFT+ENTER this formula as it is an array-formula

On 13 Maj, 11:41, Lal wrote:
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 -

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 -

I am not doing a very good job at explaining myself- is there anyway I can
privately send you the spreadsheet so that you can see the source data and
the information I am looking to achieve?

"Jarek Kujawa" wrote:

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 -

yes
use e-mail address shown

On 14 Maj, 13:08, Lal wrote:
I am not doing a very good job at explaining myself- is there anyway I can
privately send you the spreadsheet so that you can see the source data and
the information I am looking to achieve?



"Jarek Kujawa" wrote:
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 -- Ukryj cytowany tekst -

- Pokaż cytowany tekst -