count question

[del]

I need to write a formula that will do the following:
first tab of the worksheet:
Jan-08 Feb-08 Mar-08
job title1
job title 2
job title 3

2nd tab of the worksheet
Job Title Jan-08 Feb-08 Mar-08
names Job Title1 1 1 1
name1 Job title 2 1 1
name 2 Job Title 3 1 1
name 3

I need a formula that will automatically populate tab1 with the count for
each job title for each month everytime I update tab 2 with actual heads that
leave or are hired.

Hope I explained it well. Thanks for the help.

del

 

[ShaneDevenshire]

Hi Del,

Your formula would be something like:

=SUMIF(Tab2!$B$2:$N$100,$A2,Tab2!C$2:C$100)

Enter this formula in cell B2 on Tab1 and copy it down and then over as far
as necessary. I am assuming that the first column of your example is A and
the title row is row 1. The column N figure represents the last month so
change this as necessary.

 

[MikeT]

Del...There are at least several different ways to accomplish what you seek.
In short, assuming you abide by the 4 Tips below, the formula that will do
what you want is: =SUMIF(Sheet2!$B$2:$B100,Sheet1!$A2,Sheet2!C$2:C100)

A couple of important thoughts/tips:
1) a "tab" is often referred to as a "worksheet" or wksht.

2) the words or terms you use i.e., "JobTitle1", "JobTitle2", and
"JobTitle3", ect., MUST be identical on your "tab1", and "tab2" worksheets.
You can not use jobtitle1 (lowercase) on one tab and JobTitle1 (combination
uppercase/lowercase) on the other tab. Enter your JobTitles consistently
(capitalization, spaces, etc.)

3) Set up your 1st wksht ("tab1"), your "destination" worksheet, as follows:

A B C D E
1 Jan-08 Feb-08 Mar-08 Apr-08
2 JobTitle1
3 JobTitle2
4 JobTitle3
5 JobTitle4
6 JobTitle5
.. .
.. .
.. .

4) Set up your 2nd wksht ("tab1"), your "destination" worksheet, as follows:

A B C D E
F
1 Names JobTitle Jan-08 Feb-08 Mar-08 Apr-08
2 Name1 JobTitle1 1 1 1 1
3 Name2 JobTitle2 1 1
4 Name3 JobTitle3 1 1 1 1
5 Name4 JobTitle2 1 1 1 1
6 Name5 JobTitle3 1 1 1 1
.. .
.. .
.. .

The formula in cell B2 on your "tab1" wksht is:
=SUMIF(Sheet2!$B$2:$B100,Sheet1!$A2,Sheet2!C$2:C100)
The formula in cell C2 on your "tab1" wksht is:
=SUMIF(Sheet2!$B$2:$B100,Sheet1!$A2,Sheet2!D$2100)
The formula in cell D2 on your "tab1" wksht is:
=SUMIF(Sheet2!$B$2:$B100,Sheet1!$A2,Sheet2!E$2:E100)

Once the formula is entered and you test to ensure the results are correct,
simply copy and paste the formula to the other destination cells in your
"tab1" destination wksht.

Hope this helps. Let me know if you have any questions. Best Wishes.

Mike

 

[del]

Thanks for sharing these. It worked! Very clear instructions too.

del