How do I countif with two ifs?

[Lal]

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

 

[Jarek Kujawa]

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

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

 

[Jarek Kujawa]

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

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

 

[Lal]

And for the first question?

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

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

 

[Lal]

And for the first question?

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

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

 

[Don Guillett]

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

 

[Don Guillett]

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

 

[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

 

[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

 

[Lal]

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?

 

[Lal]

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?