Counting within Categories

P

Pedro's PhD

Board Regular
Joined
Jun 3, 2011
Messages
63
Have a little problem to solve here….
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p> </o:p>
I have the age of a person when they join a society (C3), and the age when they leave the society (D3). I then have age categories 25-34, 35-44, 45-54. I need a formula to return the number of years they were in each category in cell F3, G3, H3 respectively.
<o:p> </o:p>
Example: A person joins at the age of 32 and leaves at the age of 46. They are in the first category 25-34 for 3 years (so F3 to read 3), in category 35-44 for 10 years (so G3 to read 10), and in category 45-54 for 2 years (so H3 to read 2).
<o:p> </o:p>
Is this possible?
<o:p> </o:p>
Thanks in advance,
<o:p> </o:p>
Pedro
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
<TABLE style="WIDTH: 336pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=448><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20 width=64>Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Join</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Leave</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>No Years</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>25-34</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>35-44</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>45-54</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Robert</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>32</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>46</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: yellow; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>15</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>2</TD></TR></TBODY></TABLE>

If the layout is as in the table above you coudl simple add the category cells to get 15-
=SUM(F3:H3)

Is that what you after?
 
Upvote 0
There may be a more elagant way, but here is are basic formulas.

You can change the LeaveAge1, 2 and 3 to 34, 44, 54 respectively or define a name as such and give them a value of 34, 44, 54.

Good luck on your PHD!
f3:
=IF(ISNUMBER($C3),LeaveAge1-$C3+1+IF($D3>LeaveAge1,0,-LeaveAge1+$D3),"")

g3:
=IF(ISNUMBER($C3),LeaveAge2-$C3+1+IF($D3>LeaveAge2,0,-LeaveAge2+$D3)-F3,"")

h3:
=IF(ISNUMBER($C3),LeaveAge3-$C3+1+IF($D3>LeaveAge3,0,-LeaveAge3+$D3)-G3-F3,"")
Click to expand...

Steve=True
 
Upvote 0
Pedro's PhD said:
Have a little problem to solve here….
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
I have the age of a person when they join a society (C3), and the age when they leave the society (D3). I then have age categories 25-34, 35-44, 45-54. I need a formula to return the number of years they were in each category in cell F3, G3, H3 respectively.
<o:p></o:p>
Example: A person joins at the age of 32 and leaves at the age of 46. They are in the first category 25-34 for 3 years (so F3 to read 3), in category 35-44 for 10 years (so G3 to read 10), and in category 45-54 for 2 years (so H3 to read 2).
<o:p></o:p>
Is this possible?
<o:p></o:p>
Thanks in advance,
<o:p></o:p>
Pedro
Click to expand...
Try this...

Book1
CDEFGH
2___253545
3JoinLeaveTotal344454
43246153102
525262200
63351192107
740456051
Sheet1

Formula entered in E4 and copied down:

=D4-C4+1

Formula entered in F4:

=MAX(0,MIN($D4,F$3)-MAX($C4,F$2)+1)

Copy across to H4 then down as needed.
 
Upvote 0
T. Valko said:
Try this...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 54px"><COL style="WIDTH: 54px"><COL style="WIDTH: 54px"><COL style="WIDTH: 54px"><COL style="WIDTH: 54px"><COL style="WIDTH: 54px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; COLOR: #ffffff; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">_</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">25</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">35</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">45</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Join</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Leave</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">Total</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">34</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">44</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">54</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">32</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">46</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">15</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">10</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">2</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">25</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">26</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">33</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">51</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">19</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">10</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">7</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">40</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">45</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">6</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">1</TD></TR></TBODY></TABLE>


Formula entered in E4 and copied down:

=D4-C4+1

Formula entered in F4:

=MAX(0,MIN($D4,F$3)-MAX($C4,F$2)+1)

Copy across to H4 then down as needed.
Click to expand...

Steve, very useful but it returns a minus number if they do not qualify for a category.

Biff, as exceptional as ever, this formula works excellently!

I made a slight error in my posting, in the first category 25-34, a person joining at 32 should be in the first category for 2years rather than 3 (so F3 to read 2).

Apologies, anyway to correct the formula to suit this?

Thanks.... Pedro
 
Upvote 0
Pedro's PhD said:
Steve, very useful but it returns a minus number if they do not qualify for a category.

Biff, as exceptional as ever, this formula works excellently!

I made a slight error in my posting, in the first category 25-34, a person joining at 32 should be in the first category for 2years rather than 3 (so F3 to read 2).

Apologies, anyway to correct the formula to suit this?

Thanks.... Pedro
Click to expand...
Ok, then that means you don't want to count the join year as the 1st year?

Book1
CDEFGH
2___253545
3JoinLeaveTotal344454
43246142102
525261100
63351181107
740455041
Sheet1

Formula entered in E4 and copied down:

=D4-C4

Formula entered in F4:

=MAX(0,MIN($D4,F$3)-MAX($C4+1,F$2)+1)

Copy across to H4 then down as needed.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Display age range and age based on date entered and actual calendar date.
Replies
6
Views
371
dreid1011
dreid1011
Z
  • Question Question
Xlookup with multiple conditions
Replies
5
Views
258
Cubist
Cubist
T
  • Question Question
Count the ages within age range in excel
Replies
9
Views
2K
Fluff
Fluff
Z
  • Solved
Age Grouping
Replies
6
Views
726
etaf
etaf
S
  • Question Question
Inventory disposal plan
Replies
3
Views
298
Spaceballs_the_fanboy
S

Forum statistics

Threads
1,224,575
Messages
6,179,637
Members
452,934
Latest member
Jdsonne31

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top