Countif & Ranges Question

[Tyges79]

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:A4000,">="&15)-COUNTIF(JOINER!A2:A4000,">="&30)

Any assistance would be greatly appreciated.

 

[T. Valko]

=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

 

[Herbert Seidenberg]

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

 

[Tyges79]

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]

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

 

[Pete_UK]

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

 

[Herbert Seidenberg]

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