|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 13th, 2009, 10:41 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
How do I countif with two ifs?
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 
|
|
#2
May 13th, 2009, 11:12 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I countif with two ifs?
=AVERAGE(IF((B1:B5="Yes")*(C1:C5="Current"),D1
 5) )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
|
|
#3
May 13th, 2009, 11:48 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I countif with two ifs?
And for the first question?
"Jarek Kujawa" wrote: =AVERAGE(IF((B1:B5="Yes")*(C1:C5="Current"),D1 5) )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
|
|
#5
May 13th, 2009, 04:14 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I countif with two ifs?
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
|
|
#6
May 14th, 2009, 07:30 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I countif with two ifs?
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 5) )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 -
|
|
#7
May 14th, 2009, 07:43 AM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I countif with two ifs?
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 -
|
|
#8
May 14th, 2009, 12:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I countif with two ifs?
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 -
|
|
#9
May 14th, 2009, 06:28 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
How do I countif with two ifs?
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 -
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
