Hello I have a spreadsheet that I need to count up to 500 items for. For
example how many people between the ages of 19 and 29 fell. right now I am
doing ((COUNTIF(a1:a500,"19")+(COUNTIF(a1:a500,"20")) and so on. I knwo
there has to be an easier way! So the example below:
a
1 19
2 22
3 24
4 55
5 76
6 44

I would want
a b
1 19-29 3
2 30-49 1
3 50-69 1
4 70-79 1

Also if I could get help with this I also will need to count time ranges
e.i. 0100 to 0900. I know there is a way to do these, but I can nto figure
it out - thanks!!

Would suggest you to place the query range in two cells.. Suppose you have
data in Col A starting from row 1.

B1 = 19 (start range )
C1 = 29 (end range)

Use this formula in D1. Adjust the row count to suit your requirement

=COUNTIF(A1:A100,"" & B1-1)-COUNTIF(A1:A100,"" & C1)


If this post helps click Yes
---------------
Jacob Skaria


"ericaamousseau" wrote:

Hello I have a spreadsheet that I need to count up to 500 items for. For
example how many people between the ages of 19 and 29 fell. right now I am
doing ((COUNTIF(a1:a500,"19")+(COUNTIF(a1:a500,"20")) and so on. I knwo
there has to be an easier way! So the example below:
a
1 19
2 22
3 24
4 55
5 76
6 44

I would want
a b
1 19-29 3
2 30-49 1
3 50-69 1
4 70-79 1

Also if I could get help with this I also will need to count time ranges
e.i. 0100 to 0900. I know there is a way to do these, but I can nto figure
it out - thanks!!

Use 2 cells to hold your boundaries:

...........C..........D
1.......19.........29
2.......30.........49
3.......50.........69
4.......70.........79

Formula in E1:

=COUNTIF(A$1:A$6,"="&C1)-COUNTIF(A$1:A$6,""&D1)

Copy down to E4

--
Biff
Microsoft Excel MVP


"ericaamousseau" wrote in message
...
Hello I have a spreadsheet that I need to count up to 500 items for. For
example how many people between the ages of 19 and 29 fell. right now I
am
doing ((COUNTIF(a1:a500,"19")+(COUNTIF(a1:a500,"20")) and so on. I knwo
there has to be an easier way! So the example below:
a
1 19
2 22
3 24
4 55
5 76
6 44

I would want
a b
1 19-29 3
2 30-49 1
3 50-69 1
4 70-79 1

Also if I could get help with this I also will need to count time ranges
e.i. 0100 to 0900. I know there is a way to do these, but I can nto
figure
it out - thanks!!

ericaamousseau wrote:
Hello I have a spreadsheet that I need to count up to 500 items for. For
example how many people between the ages of 19 and 29 fell. right now I am
doing ((COUNTIF(a1:a500,"19")+(COUNTIF(a1:a500,"20")) and so on. I knwo
there has to be an easier way! So the example below:
a
1 19
2 22
3 24
4 55
5 76
6 44

I would want
a b
1 19-29 3
2 30-49 1
3 50-69 1
4 70-79 1

Also if I could get help with this I also will need to count time ranges
e.i. 0100 to 0900. I know there is a way to do these, but I can nto figure
it out - thanks!!


For 19 <= A <= 29 try

=SUMPRODUCT(($A$1:$A$6=19)*($A$1:$A$6<=29))

Vary the ranges and comparisons to suit.