Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
I have a column showing the ages of a number of people. I want to count the
number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
You have an overlap for people age 60. They'll be counted twice.
Over 65: =COUNTIF(rng,"65") 60 to 65: =COUNTIF(rng,"=60")-COUNTIF(rng,"65") 55 to 59: =COUNTIF(rng,"=55")-COUNTIF(rng,"59") -- Biff Microsoft Excel MVP "James" wrote in message ... I have a column showing the ages of a number of people. I want to count the number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
=COUNTIF(A1:A21,"65")
=SUMPRODUCT((A1:A2160)*(A1:A21<65)) =SUMPRODUCT((A1:A21=55)*(A1:A21<=60)) "James" skrev: I have a column showing the ages of a number of people. I want to count the number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
Could you be a bit more specific, what did not work and what was your
formula? =COUNTIF(A1:A100,"65") will count all over 65 =COUNTIF(A1:A100,"60")-COUNTIF(A1:A100,"65") will count from 61 and up to 65 (including 65) =COUNTIF(A1:A100,"55")-COUNTIF(A1:A100,"60") will count 56 to 60 (including 60) You might have to tweak = etc depending on the limits for each age group just make sure you don't double count -- Regards, Peo Sjoblom "James" wrote in message ... I have a column showing the ages of a number of people. I want to count the number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
your formula will leave out 65, change <65 to <=65
-- Regards, Peo Sjoblom "excelent" wrote in message ... =COUNTIF(A1:A21,"65") =SUMPRODUCT((A1:A2160)*(A1:A21<65)) =SUMPRODUCT((A1:A21=55)*(A1:A21<=60)) "James" skrev: I have a column showing the ages of a number of people. I want to count the number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
count age over 65
=COUNTIF(rng,"65") count age from 60 to 65 =SUM(COUNTIF(rng,{"=60","65"})*{1,-1}) count age from 55 to 60 =SUM(COUNTIF(rng,{"=55","60"})*{1,-1}) "James" wrote: I have a column showing the ages of a number of people. I want to count the number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
That will include 60 twice
=SUM(COUNTIF(rng,{"=55","=60"})*{1,-1}) will exclude 60 -- Regards, Peo Sjoblom "Teethless mama" wrote in message ... count age over 65 =COUNTIF(rng,"65") count age from 60 to 65 =SUM(COUNTIF(rng,{"=60","65"})*{1,-1}) count age from 55 to 60 =SUM(COUNTIF(rng,{"=55","60"})*{1,-1}) "James" wrote: I have a column showing the ages of a number of people. I want to count the number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
Thank you all for helping. The formula below worked for me so am able to
manipulate it for the other ranges. Thanks again! "Peo Sjoblom" wrote: Could you be a bit more specific, what did not work and what was your formula? =COUNTIF(A1:A100,"65") will count all over 65 =COUNTIF(A1:A100,"60")-COUNTIF(A1:A100,"65") will count from 61 and up to 65 (including 65) =COUNTIF(A1:A100,"55")-COUNTIF(A1:A100,"60") will count 56 to 60 (including 60) You might have to tweak = etc depending on the limits for each age group just make sure you don't double count -- Regards, Peo Sjoblom "James" wrote in message ... I have a column showing the ages of a number of people. I want to count the number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count age ranges
Array-entered in four consequtive cells (such as B1:B4) with Ctrl+Shift
+Enter. =FREQUENCY($A$1:$A$100,{54,60,65,200}) B1 = 54 and younger, functionally "under 55" B2 = between 55 and 60, inclusively B3 = between 60 and 65, inclusively B4 = between 65 and 200, functionally "over 65" On Nov 9, 12:39 pm, James wrote: I have a column showing the ages of a number of people. I want to count the number of people who a Over 65 60 to 65 and 55 to 60 The countif formula I tried is not working. Thank you in advance for any help. James |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to Count Occurences in Multiple Ranges | Excel Worksheet Functions | |||
count age ranges | Excel Discussion (Misc queries) | |||
count if in two ranges | Excel Discussion (Misc queries) | |||
count date ranges | Excel Worksheet Functions | |||
Formula that will count between dates ranges | Excel Worksheet Functions |