Sum of Certain Age range People

krishhi · Aug 4, 2011

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 · Aug 4, 2011

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

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 · Aug 4, 2011

In Excel 2007 and above you can use COUNTIFS.

What about people over 90?

krishhi · Aug 4, 2011

Wow,

Thank you very much. It is working.

krishhi · Aug 4, 2011

GlennUK said:
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 · Aug 4, 2011

Here you are:

Excel Workbook

A

B

C

D

E

F

G

H

1

Names

1 to 18

19-25

26-45

46-75

76-90

2

Name 1

44

3

Name 2

44

4

Name 3

23

5

Name 4

24

6

Name 5

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

Sheet4

krishhi · Aug 4, 2011

Yahya said:
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 · Aug 6, 2011

krishhi said:
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

Sum of Certain Age range People

krishhi

Active Member

Excel Facts

Yahya

Well-known Member

GlennUK

Well-known Member

krishhi

Active Member

krishhi

Active Member

GlennUK

Well-known Member

krishhi

Active Member

Yahya

Well-known Member

Similar threads

Forum statistics

Share this page

Sum of Certain Age range People

Active Member

Excel Facts

Well-known Member

Well-known Member

Active Member

Active Member

Well-known Member

Active Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock