Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif & Ranges Question
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 - |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Countif & Ranges Question
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countif within date ranges | Excel Worksheet Functions | |||
COUNTIF within date ranges | Charts and Charting in Excel | |||
countif with non-continous ranges | Excel Worksheet Functions | |||
Countif between ranges | Excel Discussion (Misc queries) | |||
CountIF and changing ranges | Excel Worksheet Functions |