Excel formula that counts events after a certain date?

M

micro1330

I need to set up a formula that will add up the number of people in my
spreadsheet whose birthdate is before a certain date.
 
G

Guest

micro1330 said:
I need to set up a formula that will add up the number of people in my
spreadsheet whose birthdate is before a certain date.
Click to expand...

If your birth dates are in column B, then:

= COUNTIF(B1:B100,"< 1/2/2001")

Bill
 
M

micro1330

Thank you!! I think that I asked the wrong question for what I wanted though.
Can I make this work if I want to know how many are a certain age as of
today?
Thanks again.
 
G

Guest

micro1330 said:
Thank you!! I think that I asked the wrong question for what I wanted though.
Can I make this work if I want to know how many are a certain age as of
today?
Thanks again.

:
Click to expand...


Sure... Figure what date makes people that age and plug it into that formula.
You can either do the figuring manually or ask Excel to do it for you.

= Today() - (50*365.25)

for example would calculate the date on which someone was born who is currently
50 years old.

Bill
 
M

micro1330

I can't get it to calculate correctly. Here is my formula
=COUNTIF(B4:B39,"TODAY()-(18*365.25)")
I should be getting 2 for this particular sheet and am getting 0
I also tried putting the portion in paranthesis in another cell and
directing the formula there. That also doesn't work. Where am I messing up?
 
G

Guest

micro1330 said:
I can't get it to calculate correctly. Here is my formula
=COUNTIF(B4:B39,"TODAY()-(18*365.25)")
I should be getting 2 for this particular sheet and am getting 0
I also tried putting the portion in paranthesis in another cell and
directing the formula there. That also doesn't work. Where am I messing up?

:
Click to expand...


Ok, assuming A1 contains the reference date try it this way:

={SUM(IF(B1:B100<A$1,1,0))}

Note that this is an array formula, so when you type it in you use
Shift-Ctrl-Enter -- not just the normal Enter key.

Bill
 
Top