Sum of Certain Age range People

[krishhi]

Hello Guys,

I have a excel file, Let's say, In Column A their names and In B their Ages. So, I want a formula to calculate the age group of the people like

In C1 I need 0 - 18 age
In D2 I need 19 -25 age
In E2 I need 26 -45 age
In F2 I need 46 -75 age
In G2 I need 75 -90 age

Waiting for your kind reply,

Thanks,

Krrish

 

[Yahya]

TRY THIS

Excel Workbook
	A	B	C	D	E	F	G
1	Names		1 - 18	19-25	26-45	46-75	76-90
2	Name 1	44	2	4	7	3	5
3	Name 2	44
4	Name 3	23
5	Name 4	24
6	Name 5	7
7	Name 6	57
8	Name 7	75
9	Name 8	24
10	Name 9	34
11	Name 10	43
12	Name 11	80
13	Name 12	28
14	Name 13	21
15	Name 14	62
16	Name 15	87
17	Name 16	87
18	Name 17	77
19	Name 18	6
20	Name 19	88
21	Name 20	29
22	Name 21	31
Sheet3

Excel 2007

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--($B$2:$B$22>=1),--($B$2:$B$22<=18))
D2	=SUMPRODUCT(--($B$2:$B$22>=19),--($B$2:$B$22<=25))
E2	=SUMPRODUCT(--($B$2:$B$22>=26),--($B$2:$B$22<=45))
F2	=SUMPRODUCT(--($B$2:$B$22>=46),--($B$2:$B$22<=75))
G2	=SUMPRODUCT(--($B$2:$B$22>=76),--($B$2:$B$22<=90))

 

[GlennUK]

In Excel 2007 and above you can use COUNTIFS.

What about people over 90?

 

[krishhi]

Wow,

Thank you very much. It is working.

 

[krishhi]

In Excel 2007 and above you can use COUNTIFS.

What about people over 90?

Can you provide that countifs too?

Right now, i don't have that requirement above 90

Thanks for your concern.

 

[GlennUK]

Here you are:

Excel Workbook
	A	B	C	D	E	F	G	H
1	1	Names		1 to 18	19-25	26-45	46-75	76-90
2	2	Name 1	44	2	4	7	3	5
3	3	Name 2	44
4	4	Name 3	23
5	5	Name 4	24
6	6	Name 5	7
7	7	Name 6	57
8	8	Name 7	75
9	9	Name 8	24
10	10	Name 9	34
11	11	Name 10	43
12	12	Name 11	80
13	13	Name 12	28
14	14	Name 13	21
15	15	Name 14	62
16	16	Name 15	87
17	17	Name 16	87
18	18	Name 17	77
19	19	Name 18	6
20	20	Name 19	88
21	21	Name 20	29
22	22	Name 21	31
Sheet4

 

[krishhi]

TRY THIS

Excel Workbook
	A	B	C	D	E	F	G
1	Names		1 - 18	19-25	26-45	46-75	76-90
2	Name 1	44	2	4	7	3	5
3	Name 2	44
4	Name 3	23
5	Name 4	24
6	Name 5	7
7	Name 6	57
8	Name 7	75
9	Name 8	24
10	Name 9	34
11	Name 10	43
12	Name 11	80
13	Name 12	28
14	Name 13	21
15	Name 14	62
16	Name 15	87
17	Name 16	87
18	Name 17	77
19	Name 18	6
20	Name 19	88
21	Name 20	29
22	Name 21	31
Sheet3

Excel 2007

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(--($B$2:$B$22>=1),--($B$2:$B$22<=18))
D2	=SUMPRODUCT(--($B$2:$B$22>=19),--($B$2:$B$22<=25))
E2	=SUMPRODUCT(--($B$2:$B$22>=26),--($B$2:$B$22<=45))
F2	=SUMPRODUCT(--($B$2:$B$22>=46),--($B$2:$B$22<=75))
G2	=SUMPRODUCT(--($B$2:$B$22>=76),--($B$2:$B$22<=90))

Thank you very much,

If you don't mind, can you please explain the formula? I don't understand this part [ --] what is the symbols?

Thanks in advance,
Waiting for your reply

 

[Yahya]

Thank you very much,

If you don't mind, can you please explain the formula? I don't understand this part [ --] what is the symbols?

Thanks in advance,
Waiting for your reply

see this link please
http://www.xldynamic.com/source/xld.SUMPRODUCT.html