Formula to calculate age ranges

[mnoah]

Hello,

Looking to calculate age ranges of people in an excel list, but it should only count the non-duplicated names. So for the example below, it should only count "Same Person" and "Other Same Person" once so that it does not give me a false record of people in that age range.

I would like to accomplish this with a formula and not a pivot table, and no helping columns/calculations. I've tried using some variations of sumproduct and countif, but to no avail.

[TABLE="width: 411"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Age[/TD]
[TD][/TD]
[TD]Age Range[/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]Same Person[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]20-30[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Other Same Person[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]31-40[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Same Person[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]41-50[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Same Person[/TD]
[TD]75[/TD]
[TD][/TD]
[TD]51-60[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]John Doe[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]61+[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]Elaine Smith[/TD]
[TD]23[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Wendy Troupe[/TD]
[TD]50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Levi Smith[/TD]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Other Same Person[/TD]
[TD]20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

Based on your sample, try this, copied down...
=COUNTIFS($B$2:$B$10,">="&--LEFT(D2,2),$B$2:$B$10,"<="&IF(ISNUMBER(FIND("+",D2)),150,--RIGHT(D2,2)))

 

[mnoah]

Based on your sample, try this, copied down...
=COUNTIFS($B$2:$B$10,">="&--LEFT(D2,2),$B$2:$B$10,"<="&IF(ISNUMBER(FIND("+",D2)),150,--RIGHT(D2,2)))

Thank you for taking the time! But unfortunately, this count's the duplicated people's ages. The count for age range 61+ should only be 1, since "Same Person" should not be counted three times.

For example, I use this code to do something similar. It finds unduplicated counts of gender based on non-duplicated names in column B

=SUMPRODUCT((I2:I800="Male")/COUNTIF(B2:B800,B2:B800&""))

 

[mnoah]

Think I figured it out, but can anyone help me out a bit? Would like to add the red part highlighted below.

BTW, this is an array:

=SUM((B2:B800>=61 [COLOR=#ff0000]BUT LESS THAN 75??[/COLOR])/COUNTIF(A2:A800,A2:A800&""))

 

[AhoyNC]

Maybe something like this.
These are array formulas and must be entered with CTRL-SHIFT-ENTER (command-return on MAC).

In this example formula in E2 can be copied down to E5. Formula in E6 is different since no upper limit.

Excel Workbook
	A	B	C	D	E
1	Name	Age		Age Range	Count
2	Same Person	75		20-30	3
3	Other Same Person	20		31-40	0
4	Same Person	75		41-50	2
5	Same Person	75		51-60	0
6	John Doe	50		61+	1
7	Elaine Smith	23
8	Wendy Troupe	50
9	Levi Smith	25
10	Other Same Person	20
Sheet

 

[mnoah]

AhoyNC,

Works beautifully. Thank you!

 

[Aladin Akyurek]

Thank you for taking the time! But unfortunately, this count's the duplicated people's ages. The count for age range 61+ should only be 1, since "Same Person" should not be counted three times.

For example, I use this code to do something similar. It finds unduplicated counts of gender based on non-duplicated names in column B

=SUMPRODUCT((I2:I800="Male")/COUNTIF(B2:B800,B2:B800&""))

It's faster to use the FREQUENCY formula... Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-($B$2:$B$800=""),IF($I$2:$I$800="Male",
    MATCH($B$2:$B$800,$B$2:$B$800,0))),ROW($B$2:$B$800)-ROW($B$2)+1),1))

Also, why not set up the age range in a 2-column representation for faster calculations?

[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][/tr]
[tr][td]

1​

[/td][td] Name[/td][td] Age[/td][td] [/td][td] Age Range (from)[/td][td] to[/td][td] Count[/td][/tr]


[tr][td]

2​

[/td][td] Same Person[/td][td] 75[/td][td] [/td][td] 20[/td][td] 30[/td][td]

3​

[/td][/tr]


[tr][td]

3​

[/td][td] Other Same Person[/td][td] 20[/td][td] [/td][td] 31[/td][td] 40[/td][td]

0​

[/td][/tr]


[tr][td]

4​

[/td][td] Same Person[/td][td] 75[/td][td] [/td][td] 41[/td][td] 50[/td][td]

2​

[/td][/tr]


[tr][td]

5​

[/td][td] Same Person[/td][td] 75[/td][td] [/td][td] 51[/td][td] 60[/td][td]

0​

[/td][/tr]


[tr][td]

6​

[/td][td] John Doe[/td][td] 50[/td][td] [/td][td] 61[/td][td] 1.00E+308[/td][td]

1​

[/td][/tr]


[tr][td]

7​

[/td][td] Elaine Smith[/td][td] 23[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

8​

[/td][td] Wendy Troupe[/td][td] 50[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

9​

[/td][td] Levi Smith[/td][td] 25[/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]


[tr][td]

10​

[/td][td] Other Same Person[/td][td] 20[/td][td] [/td][td][/td][td][/td][td][/td][/tr]
[/table]

E6 contains: 9.99999999999999E+307, which is a useful Excel contant.

In F2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-($A$2:$A$10=""),IF($B$2:$B$10>=$D2,
    IF($B$2:$B$10<=$E2,MATCH($A$2:$A$10,$A$2:$A$10,0)))),
   ROW($A$2:$A$10)-ROW($A$2)+1),1))

 

[FDibbins]

oops missed the unique bit...
=SUM(--(FREQUENCY(IF((($B$2:$B$10>=--LEFT(D2,2))*($B$2:$B$10<=IF(ISNUMBER(FIND("+",D2)),160,--RIGHT(D2,2)))),$B$2:$B$10),$B$2:$B$10)>0))
ARRAY formula, using CTRL SHIFT ENTER, not just enter, then copied down

 

[AhoyNC]

You're welcome. I do agree with Aladin it would be better if you could split your age range into 2 columns which would eliminate the need for the LEFT and RIGHT functions.
Thanks for the feedback.

 

[FDibbins]

You're welcome. I do agree with Aladin it would be better if you could split your age range into 2 columns which would eliminate the need for the LEFT and RIGHT functions.
Thanks for the feedback.

Agreed, because then you could do away with the LEFT() and RIGHT functions in my suggestion too