Assistance: Count if numbers are within a range

[Simmeon]

Hi everyone,

I would be grateful for your help.

What I'm trying to accomplish:


I want to count the numbers from A:A

If the number are between 1 and 6
If the number are between 7 and 12
If the number are between 13 and 18
If the number are between 19 and 24
If the number are between 25 and 30
If the number are between 31 and 66

Example:
The Yellow Column for "25 to 30" would be 4 because in A:A we have the numbers 27,25,26,27

 

[Marcelo Branco]

Welcome to Mr Excel

Maybe this

C2 copied across
=COUNTIFS($A:$A,">="&LEFT(C1,2),$A:$A,"<="&RIGHT(C1,2))

Hope this helps

M.

 

[hippiehacker]

for the first one the others I hope you can ammend the formula

=COUNTIFS(A:A;">=1";A:A;"<=6")

 

[AlanY]

an alternative

Excel 2012
	A	B	C	D	E	F	G	H
1	36		6	12	18	24	30	36
2	18		1	1	1	2	4	3
3	27
4	12
5	25
6	34
7	31
8	26
9	24
10	23
11	27
12	5
Sheet5

Cell Formulas
Range	Formula
C2	=INDEX(FREQUENCY($A$1:$A$12,$C$1:$H$1),COLUMN(C1)-COLUMN($C$1)+1)

 

[Simmeon]

Hi Marcelo Branco ,

Thank you very much.
This has worked perfectly.

Forever grateful.

Welcome to Mr Excel

Maybe this

C2 copied across
=COUNTIFS($A:$A,">="&LEFT(C1,2),$A:$A,"<="&RIGHT(C1,2))

Hope this helps

M.

 

[Simmeon]

Thank you AlanY so much for your solution.
I'll save this as well.

an alternative

Excel 2012

	A	B	C	D	E	F	G	H
1	36		6	12	18	24	30	36
2	18		1	1	1	2	4	3
3	27
4	12
5	25
6	34
7	31
8	26
9	24
10	23
11	27
12	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula C2 =INDEX(FREQUENCY($A$1:$A$12,$C$1:$H$1),COLUMN(C1)-COLUMN($C$1)+1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Simmeon]

hippiehacker ,

Sadly that one did not work.
Thanks for your help.

for the first one the others I hope you can ammend the formula

=COUNTIFS(A:A;">=1";A:A;"<=6")

 

[AlanY]

you're welcome

 

[Marcelo Branco]

Hi Marcelo Branco ,

Thank you very much.
This has worked perfectly.

Forever grateful.

You are welcome. Thanks for the feedback.

M.