| RE: Countif & Ranges Question |
|
 |
|
 |
|
 |
|
 |
Group: microsoft.public.excel.misc · Group Profile
Author: Tyges79Tyges79 Date: Sep 23, 2008 01:30
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:A4000,">="&15)-COUNTIF(JOINER!A2:A4000,">="&30)
>
> Any assistance would be greatly appreciated.
| 
