Function that counts values and returns them

[cristitoner]

Hello! I work for a non-profit organization that helps special children. I have to make a table that contains children's age (among other information). I don't know how to explain what I want to do, so I will write an example:

Name Age
John Smith 5
John Smith 13
John Smith 17

Age Total
0-2 years 0
3-6 years 1
7-14 years 1
15-18 years 1

Is there a formula that automatically calculates how many children are in what age categories like in the second table? Sorry for the poor explanation, I hope I explained my problem correctly and you can help me. Thank you!

 

[jeffreybrown]

Hello & Welcome to the Forum,

Maybe this...

F2:=COUNTIF($B$2:$B$14,">"&E2)-SUM(F3:$F$5) copied down to F4
F5:=COUNTIF($B$2:$B$14,">"&E5)

You can hide column E

Data Range
[Table="class: grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]

Name Age​

[/td][td]

Age​

[/td][td]

​

[/td][td]

Age​

[/td][td]

​

[/td][td]

Total​

[/td][/tr]

[tr][td]

2​

[/td][td]

John Smith​

[/td][td]

5​

[/td][td]

​

[/td][td]

0-2​

[/td][td]

0​

[/td][td]

1​

[/td][/tr]

[tr][td]

3​

[/td][td]

John Smith​

[/td][td]

1​

[/td][td]

​

[/td][td]

3-6​

[/td][td]

3​

[/td][td]

2​

[/td][/tr]

[tr][td]

4​

[/td][td]

John Smith​

[/td][td]

4​

[/td][td]

​

[/td][td]

7-14​

[/td][td]

7​

[/td][td]

2​

[/td][/tr]

[tr][td]

5​

[/td][td]

John Smith​

[/td][td]

18​

[/td][td]

​

[/td][td]

15-18​

[/td][td]

15​

[/td][td]

8​

[/td][/tr]

[tr][td]

6​

[/td][td]

John Smith​

[/td][td]

19​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

7​

[/td][td]

John Smith​

[/td][td]

8​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

8​

[/td][td]

John Smith​

[/td][td]

21​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

9​

[/td][td]

John Smith​

[/td][td]

22​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

10​

[/td][td]

John Smith​

[/td][td]

9​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

11​

[/td][td]

John Smith​

[/td][td]

24​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

12​

[/td][td]

John Smith​

[/td][td]

25​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

13​

[/td][td]

John Smith​

[/td][td]

26​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]

[tr][td]

14​

[/td][td]

John Smith​

[/td][td]

27​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][td]

​

[/td][/tr]
[/table]

I know see I did not stop at 15-18

 

[gaz_chops]

Welcome to the forum.

Depending on the version of Excel you are using, you could use countifs.

[TABLE="width: 130"]
<tbody>[TR]
[TD="width: 65"]Name Age[/TD]
[TD="width: 65"][/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]John Smith[/TD]
[TD="align: right"]17[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Age Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0-2 years 0[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]3-6 years 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]7-14 years 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]15-18 years 1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

=COUNTIFS($B$2:$B$4,">0",$B$2:$B$4,"<=2")

=COUNTIFS($B$2:$B$4,">=3",$B$2:$B$4,"<=6")

=COUNTIFS($B$2:$B$4,">=7",$B$2:$B$4,"<=14")

=COUNTIFS($B$2:$B$4,">=15",$B$2:$B$4,"<=18")

You need to watch for the gaps between <=2 & >=3 etc

 

[gaz_chops]

Or easier if you split the from & to ages into 2 columns

[TABLE="width: 195"]
<!--StartFragment--> <colgroup><col width="65" span="3" style="width:65pt"> </colgroup><tbody>[TR]
  [TD="width: 65"]Name Age[/TD]
  [TD="width: 65"][/TD]
  [TD="width: 65"][/TD]
 [/TR]
 [TR]
  [TD]John Smith[/TD]
  [TD][/TD]
  [TD="align: right"]3[/TD]
 [/TR]
 [TR]
  [TD]John Smith[/TD]
  [TD][/TD]
  [TD="align: right"]13[/TD]
 [/TR]
 [TR]
  [TD]John Smith[/TD]
  [TD][/TD]
  [TD="align: right"]17[/TD]
 [/TR]
 [TR]
  [TD][/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD]Age Total[/TD]
  [TD][/TD]
  [TD][/TD]
 [/TR]
 [TR]
  [TD="align: right"]0[/TD]
  [TD="align: right"]2[/TD]
  [TD="align: right"]0[/TD]
 [/TR]
 [TR]
  [TD="align: right"]3[/TD]
  [TD="align: right"]6[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="align: right"]7[/TD]
  [TD="align: right"]14[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
 [TR]
  [TD="align: right"]15[/TD]
  [TD="align: right"]18[/TD]
  [TD="align: right"]1[/TD]
 [/TR]
<!--EndFragment--></tbody>[/TABLE]

Then use
=COUNTIFS($C$2:$C$4,">="&A7,$C$2:$C$4,"<="&B7)
Copied down

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][/tr]
[tr][td]

1​

[/td][td]Name Age[/td][td]Age[/td][td][/td][td]age:[/td][td]0-2 years[/td][td]3-6 years[/td][td]7-14 years[/td][td]15-18 years[/td][/tr]


[tr][td]

2​

[/td][td]john[/td][td]

5​

[/td][td][/td][td][/td][td]

0​

[/td][td]

3​

[/td][td]

7​

[/td][td]

15​

[/td][/tr]


[tr][td]

3​

[/td][td]dave[/td][td]

1​

[/td][td][/td][td][/td][td]

2​

[/td][td]

6​

[/td][td]

14​

[/td][td]

18​

[/td][/tr]


[tr][td]

4​

[/td][td]numan[/td][td]

4​

[/td][td][/td][td]total:[/td][td]

2​

[/td][td]

2​

[/td][td]

4​

[/td][td]

1​

[/td][/tr]


[tr][td]

5​

[/td][td]damon[/td][td]

18​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

6​

[/td][td]vince[/td][td]

19​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

7​

[/td][td]nathan[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

8​

[/td][td]marcus[/td][td]

8​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

9​

[/td][td]donna[/td][td]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

10​

[/td][td]marga[/td][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

11​

[/td][td]anna[/td][td]

24​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

12​

[/td][td]karl[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

13​

[/td][td]mete[/td][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

14​

[/td][td]jan[/td][td]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

E4, copied across:

=INDEX(FREQUENCY($B$2:$B$14,E2:E3),2)<strike></strike>

 

[cristitoner]

Hey, the COUNTIFS function worked great. Thank you very much guys, I am grateful. Now, is there any way to do this without changing the column number when I calculate, and for the program to "notice" automatically when the numbers start and stop? I tried, copy - paste and it doesn't work propperly. I know how my request sounds, sorry. This is how my table looks like and what I did so far

A B C D E F G H I J K L M N O P Q R S T U V W
1 X X X X X X X X X X X 10 X X X X X X X X X X X
2 X X XX X X X X X X X X 12 X X X X X X X X X X X
3
4 TOTAL 0-3
5 3 - 7 ANI
6 7 - 14 ANI 2
7 14 - 18 ANI
8 18 - 26 ANI
9 TOTAL 2
10 X X X X X X X X X X X 15 X X X X X X X X X X X
11 X X XX X X X X X X X X 16 X X X X X X X X X X X
12
13 TOTAL 0-3 ANI
14 3 - 7 ANI
15 7 - 14 ANI
16 14 - 18 ANI 2
17 18 - 26 ANI
18 TOTAL 2

In order to calculate correctly, I used the following functions:

=COUNTIFS(L1:L2,">="&1,L1:L2,"<="&2)
=COUNTIFS(L1:L2,">="&3,L1:L2,"<="&6)
=COUNTIFS(L1:L2,">="&7,L1:L2,"<="&13)
=COUNTIFS(L1:L2,">="&14,L1:L2,"<="&17)
=COUNTIFS(L1:L2,">="&18,L1:L2,"<="&24)

and for the other lines

=COUNTIFS(L10:L11,">="&1,L10:L11,"<="&2)
=COUNTIFS(L10:L11,">="&3,L10:L11,"<="&6)
=COUNTIFS(L10:L11,">="&7,L10:L11,"<="&13)
=COUNTIFS(L10:L11,">="&14,L10:L11,"<="&17)
=COUNTIFS(L10:L11,">="&18,L10:L11,"<="&24)

My real table is diffrent, some parts that I have to calculate have more than 100 lines, and in total , the document has a few thousand lines. Is there any way that I can change this formula so that I won't have to manually enter the line numbers? I tried copy - paste and the first part had 13 lines and the second part had 23 lines but although it automatically changed the line numbers it only calculated for the last 13 lines. I use OFFICE 2010

 

[cristitoner]

Maybe my example is too complicated, so ths way it would be easier. So I have this table, and for the automatic calculation of how many children are in any age group I used this function


=COUNTIFS(C2:C3,">="&1,C2:C3,"<="&2)
=COUNTIFS(C2:C3,">="&3,C2:C3,"<="&6)
=COUNTIFS(C2:C3,">="&7,C2:C3,"<="&13)
=COUNTIFS(C2:C3,">="&14,C2:C3,"<="&17)
=COUNTIFS(C2:C3,">="&18,C2:C3,"<="&24)

and for the other lines

=COUNTIFS(C12:C13,">="&1,C12:C13,"<="&2)
=COUNTIFS(C12:C13,">="&3,C12:C13,"<="&6)
=COUNTIFS(C12:C13,">="&7,C12:C13,"<="&13)
=COUNTIFS(C12:C13,">="&14,C12:C13,"<="&17)
=COUNTIFS(C12:C13,">="&18,C12:C13,"<="&24)


Is there any way to rewrite this calculation so I won't need to manually write the line numbers for each calculation (here I have C2:C3 and C12:C13). As you may imagine, my "real" table is much more complicated, and the number of lines is diffrent for each calculation.

For example, I have a portion of 13 lines to calculate, then I have a portion of 23 lines. If I write the function with the numbers for the 13 lines I need to calculate, then if I copy and paste it for the other 23 lines, it will only calculate the last 13 lines of the 23, and I should manually rewrite the line parameters.


[table="width: 500, class: Grid"]
[tr]
[td][/td]
[td]A[/td]
[td]B[/td]
[td]C[/td]
[td]D[/td]
[/tr]
[tr]
[td]1[/td]
[td]x[/td]
[td]x[/td]
[td]age[/td]
[td]x[/td]
[/tr]
[tr]
[td]2[/td]
[td]x[/td]
[td]x[/td]
[td]15[/td]
[td]x[/td]
[/tr]
[tr]
[td]3[/td]
[td]x[/td]
[td]x[/td]
[td]10[/td]
[td]x[/td]
[/tr]
[tr]
[td]4[/td]
[td]AGE GROUP[/td]
[td]TOTAL[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]5[/td]
[td]0-2 Y[/td]
[td]0[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]6[/td]
[td]3-6 Y[/td]
[td]0[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]7[/td]
[td]7-13 Y[/td]
[td]1[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]8[/td]
[td]14-17 Y[/td]
[td]1[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]9[/td]
[td]18-24[/td]
[td]0[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]10[/td]
[td]TOTAL[/td]
[td]2[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]11[/td]
[td]x[/td]
[td]x[/td]
[td]age[/td]
[td]x[/td]
[/tr]
[tr]
[td]12[/td]
[td]x[/td]
[td]x[/td]
[td]15[/td]
[td]x[/td]
[/tr]
[tr]
[td]13[/td]
[td]x[/td]
[td]x[/td]
[td]2[/td]
[td]x[/td]
[/tr]
[tr]
[td]14[/td]
[td]AGE GROUP[/td]
[td]TOTAL[/td]
[td][/td]
[td][/td]
[/tr]
[tr]
[td]15[/td]
[td]0-2 Y[/td]
[td]1[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]16[/td]
[td]3-6 Y[/td]
[td]0[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]17[/td]
[td]7-13 Y[/td]
[td]0[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]18[/td]
[td]14-17 Y[/td]
[td]1[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]19[/td]
[td]18-24[/td]
[td]0[/td]
[td][/td]
[td][/td]

[/tr]
[tr]
[td]20[/td]
[td]TOTAL[/td]
[td]2[/td]
[td][/td]
[td][/td]
[/tr]
[/TABLE]

 

[Aladin Akyurek]

Try to set up I proposed and the formula which is way faster than a COUNTIFS formula.

 

[cristitoner]

Is there any way to set up the formula you proposed, =INDEX(FREQUENCY($B$2:$B$14,E2:E3),2), to match my example? I don't have the knowledge to make it work from your example, and don't understand how it works. Sorry.