CountIf

[Francis Hookham]

I should know better!

Column of ages named 'Age'

30-39 =countif(and(age<30,age>40)....no that's no good
40-49
50-59
60-69
etc

I ought to be able to do this but - maybe it's my age!

Francis Hookham

 

[Domenic]

Try...

=SUMPRODUCT(--(Age>=30),--(Age<40))

Hope this helps!

 

[Mike Middleton]

Francis -

(1) The example from Excel's Help is

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")

which you could adapt as

=COUNTIF(DataRange,"<40")-COUNTIF(DataRange,"<30")

(2) Or, enter the upper bounds of each interval in a column, and use the
Histogram tool of the Analysis ToolPak.

(3) Or, use my free Better Histogram add-in from www.treeplan.com.

(4) Or, use the array-entered FREQUENCY worksheet function.

- Mike
www.mikemiddleton.com

 

[Bob Greenblatt]

Francis -

(1) The example from Excel's Help is

=COUNTIF(B2:B5,">=32")-COUNTIF(B2:B5,">85")

which you could adapt as

=COUNTIF(DataRange,"<40")-COUNTIF(DataRange,"<30")

(2) Or, enter the upper bounds of each interval in a column, and use the
Histogram tool of the Analysis ToolPak.

(3) Or, use my free Better Histogram add-in from www.treeplan.com.

(4) Or, use the array-entered FREQUENCY worksheet function.

- Mike
www.mikemiddleton.com

From your example, it looks like the ages are stored as text. Is this really
the case where the cell contents is 30-39? If so, you will need vba code to
do what you want, to use alternate columns to parse them into separate
columns for max and min values.

 

[sramsay]

From your example, it looks like the ages are stored as text. Is this really
the case where the cell contents is 30-39? If so, you will need vba code to
do what you want, to use alternate columns to parse them into separate
columns for max and min values.

 

[sramsay]

If the cell contents are text you can create a separate column with
just the first two characters or the last two as numerical values.

Use the formula =value(left(DataRange,2)) to extract 30 from 30-39 or
=value(right(DataRange,2) to extract 39 from the same cell. You can
then use the countif function to count your new column of numerical
values.

Scott Ramsay