ExcelBanter - Countif & Ranges Question

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Countif & Ranges Question (https://www.excelbanter.com/excel-discussion-misc-queries/202942-countif-ranges-question.html)

Countif & Ranges Question

I am doing some statistical reporting on the age of staff joining the
organisation. I have two lines of data which show the age and also the
classification level of the staff. To calculate the age ranges, I used the
following formula which gave the correct result below:
A1 B1
49 CLEANER
49 CLEANER
34 SENIOR ADMIN
34 TRAINER
47 TRAINER
44 TRAINER
53 TRAINER
34 TRAINER
54 TRAINER

=COUNTIF(JOINER!A2:A4000,"="&20)-COUNTIF(JOINER!A2:A4000,"="&30)
Result:
20 - 30 = 6
30 - 40 = 10
40 - 50 = 15
50+ = 8

Now I need to report on how many people in each category (TRAINER, CLEANER
etc) have left in the organisation by age group

eg (below) Number of teaching staff in the following catagories:

Desired result:
TRAINER Staff: CLEANER Staff
20 - 30 = 0 20 - 30 = 1
30 - 40 = 2 30 - 40 = 3
40 - 50 = 2 40 - 50 = 5
50+ = 2 50+ = 2

I have tried this formula, but it doesn't seem to be working. I'm trying to
get excel to just concentrate on the TRAINER ages and then sort them into age
groups.
=COUNTIF(JOINER!B2:B400="TRAINER")*(JOINER!A2:A400 0,"="&15)-COUNTIF(JOINER!A2:A4000,"="&30)

Any assistance would be greatly appreciated.

Countif & Ranges Question

=COUNTIF(JOINER!A2:A4000,"="&20)-COUNTIF(JOINER!A2:A4000,"="&30)
Result:
20 - 30 = 6
30 - 40 = 10
40 - 50 = 15
50+ = 8

Your table is "deceiving". You have overlapping intervals. Even though your
formula is counting 20 to 29 and that's probably what you want, you should
make the table with those same intervals.

Also, it would be better to put the intervals in 2 cells:

.........A......B
1.....20.....29
2.....30.....39
3.....40.....49
4.....50.....100

That way you can use the same formula and copy it for all the intervals
instead of using a separate formula for each interval:

=COUNTIF(JOINER!A$2:A$4000,"="&A1)-COUNTIF(JOINER!A$2:A$4000,""&B1)

Copy down as needed.

For your other summary...

..............A......B
1......Trainer.....
2.........20.....29
3.........30.....39
4.........40.....49
5........50.....100

=SUMPRODUCT(--(JOINER!B$2:B$4000=A$1),--(JOINER!A$2:A$4000=A$2),--(JOINER!A$2:A$4000<=B$2))

Copy down as needed

--
Biff
Microsoft Excel MVP

"Tyges79" wrote in message
...
I am doing some statistical reporting on the age of staff joining the
organisation. I have two lines of data which show the age and also the
classification level of the staff. To calculate the age ranges, I used the
following formula which gave the correct result below:
A1 B1
49 CLEANER
49 CLEANER
34 SENIOR ADMIN
34 TRAINER
47 TRAINER
44 TRAINER
53 TRAINER
34 TRAINER
54 TRAINER

=COUNTIF(JOINER!A2:A4000,"="&20)-COUNTIF(JOINER!A2:A4000,"="&30)
Result:
20 - 30 = 6
30 - 40 = 10
40 - 50 = 15
50+ = 8

Now I need to report on how many people in each category (TRAINER, CLEANER
etc) have left in the organisation by age group

eg (below) Number of teaching staff in the following catagories:

Desired result:
TRAINER Staff: CLEANER Staff
20 - 30 = 0 20 - 30 = 1
30 - 40 = 2 30 - 40 = 3
40 - 50 = 2 40 - 50 = 5
50+ = 2 50+ = 2

I have tried this formula, but it doesn't seem to be working. I'm trying
to
get excel to just concentrate on the TRAINER ages and then sort them into
age
groups.
=COUNTIF(JOINER!B2:B400="TRAINER")*(JOINER!A2:A400 0,"="&15)-COUNTIF(JOINER!A2:A4000,"="&30)

Any assistance would be greatly appreciated.

Countif & Ranges Question

Use Excel 2007 and Pivot Table.
No formulas needed:
http://www.savefile.com/files/1794455

Countif & Ranges Question

Hmm, I don't really understand this formula. I am probably a bit vague in
explaining what I'm looking for. My spreadsheet looks like this:
A1 B1 C1
name age position
BOB 24 CLEANER
SUE 45 TEACHER
ANNE 31 ADMINISTRATION
KEN 36 CLEANER
KELLY 55 TEACHER
ROBERT 22 TEACHER
SANDY 33 CLEANER
ROD 33 TEACHER
MARK 65 ADMINISTRATION
LES 45 TEACHER

I would like to count how many teachers fall within the agegroups of 20-30,
30-40, 40 - 50 and 60+ to create a table that looks like this:

TEACHERS:
A1 B1
20 - 30 1
30 - 40 1
40 - 50 2
50 - 60 1
60+ 0

CLEANERS:
A1 B1
20 - 30 1
30 - 40 2
40 - 50 0
50 - 60 0
60+ 0

Is there anything I can add to the
=COUNTIF(JOINER!A2:A4000,"="&20)-COUNTIF(JOINER!A2:A4000,"="&30) formla
that will siphon out "teachers", "cleaners" and "administration" ??

"Tyges79" wrote:

I am doing some statistical reporting on the age of staff joining the
organisation. I have two lines of data which show the age and also the
classification level of the staff. To calculate the age ranges, I used the
following formula which gave the correct result below:
A1 B1
49 CLEANER
49 CLEANER
34 SENIOR ADMIN
34 TRAINER
47 TRAINER
44 TRAINER
53 TRAINER
34 TRAINER
54 TRAINER

=COUNTIF(JOINER!A2:A4000,"="&20)-COUNTIF(JOINER!A2:A4000,"="&30)
Result:
20 - 30 = 6
30 - 40 = 10
40 - 50 = 15
50+ = 8

Now I need to report on how many people in each category (TRAINER, CLEANER
etc) have left in the organisation by age group

eg (below) Number of teaching staff in the following catagories:

Desired result:
TRAINER Staff: CLEANER Staff
20 - 30 = 0 20 - 30 = 1
30 - 40 = 2 30 - 40 = 3
40 - 50 = 2 40 - 50 = 5
50+ = 2 50+ = 2

I have tried this formula, but it doesn't seem to be working. I'm trying to
get excel to just concentrate on the TRAINER ages and then sort them into age
groups.
=COUNTIF(JOINER!B2:B400="TRAINER")*(JOINER!A2:A400 0,"="&15)-COUNTIF(JOINER!A2:A4000,"="&30)

Any assistance would be greatly appreciated.

Countif & Ranges Question

Thank you Herbert. Unfortunatly we only have Excel 2003 and can't get this to
work. Thank you though :)

"Herbert Seidenberg" wrote:

Use Excel 2007 and Pivot Table.
No formulas needed:
http://www.savefile.com/files/1794455

Countif & Ranges Question

You could do it this way:

=SUMPRODUCT((JOINER!B$2:B$4000=20)*(JOINER!B$2:B$ 4000<30)*(JOINER!C
$2:C$4000="Teacher"))

and make the obvious changes to Ages and Position for your other
counts.

Hope this helps.

Pete

On Sep 23, 12:30*am, Tyges79
wrote:
Hmm, I don't really understand this formula. I am probably a bit vague in
explaining what I'm looking for. My spreadsheet looks like this:
* A1 * * * * * * * * * * * *B1 * * * * * * * * * * * * C1
name * * * * * * * * * * age * * * * * * * * * *position *
BOB * * * * * * * * * * * *24 * * * * * * * * * * *CLEANER
SUE * * * * * * * * * * * *45 * * * * * * * * * * *TEACHER
ANNE * * * * * * * * * * *31 * * * * * * * * * * *ADMINISTRATION
KEN * * * * * * * * * * * *36 * * * * * * * * * * *CLEANER
KELLY * * * * * * * * * * 55 * * * * * * * * * * *TEACHER
ROBERT * * * * * * * * 22 * * * * * * * * * * * TEACHER
SANDY * * * * * * * * * 33 * * * * * * * * * * * CLEANER *
ROD * * * * * * * * * * * 33 * * * * * * * * * * *TEACHER
MARK * * * * * * * * * * 65 * * * * * * * * * * *ADMINISTRATION
LES * * * * * * * * * * * *45 * * * * * * * * * * *TEACHER

I would like to count how many teachers fall within the agegroups of 20-30,
30-40, 40 - 50 and 60+ to create a table that looks like this:

TEACHERS:
* *A1 * * * * * *B1
20 - 30 * * * * 1
30 - 40 * * * * 1
40 - 50 * * * * 2
50 - 60 * * * * 1
60+ * * * * * * *0

CLEANERS:
A1 * * * * * *B1
20 - 30 * * * * 1
30 - 40 * * * * 2
40 - 50 * * * * 0
50 - 60 * * * * 0
60+ * * * * * * *0

Is there anything I can add to the
=COUNTIF(JOINER!A2:A4000,"="&20)-COUNTIF(JOINER!A2:A4000,"="&30) formla
that will siphon out "teachers", "cleaners" and "administration" ??

"Tyges79" wrote:
I am doing some statistical reporting on the age of staff joining the
organisation. I have two lines of data which show the age and also the
classification level of the staff. To calculate the age ranges, I used the
following formula which gave the correct result below:
A1 * * * * * * B1
49 CLEANER
49 CLEANER
34 SENIOR ADMIN
34 TRAINER
47 TRAINER
44 TRAINER
53 TRAINER
34 TRAINER
54 TRAINER

=COUNTIF(JOINER!A2:A4000,"="&20)-COUNTIF(JOINER!A2:A4000,"="&30)
Result:
20 - 30 = 6
30 - 40 = 10
40 - 50 = 15
50+ = 8

Now I need to report on how many people in each category (TRAINER, CLEANER
etc) have left in the organisation by age group

eg (below) Number of teaching staff in the following catagories:

Desired result:
TRAINER Staff: * * * *CLEANER Staff
20 - 30 = 0 * * * * * * *20 - 30 = 1
30 - 40 = 2 * * * * * * *30 - 40 = 3
40 - 50 = 2 * * * * * * *40 - 50 = 5
50+ = 2 * * * * * * * * * 50+ = 2

I have tried this formula, but it doesn't seem to be working. I'm trying to
get excel to just concentrate on the TRAINER ages and then sort them into age
groups.
=COUNTIF(JOINER!B2:B400="TRAINER")*(JOINER!A2:A400 0,"="&15)-COUNTIF(JOINER*!A2:A4000,"="&30)

Any assistance would be greatly appreciated.- Hide quoted text -

- Show quoted text -

Countif & Ranges Question

Here is the 2003 version.
Still no formulas:
http://www.savefile.com/files/1802632