Excel formula that counts events after a certain date?
 

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.

micro1330 wrote:
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.

If your birth dates are in column B, then:

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

Bill

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.

"Bill Martin -- (Remove NOSPAM from addre" wrote:

micro1330 wrote:
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.

If your birth dates are in column B, then:

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

Bill

micro1330 wrote:
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.

"Bill Martin -- (Remove NOSPAM from addre" wrote:


micro1330 wrote:

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.

If your birth dates are in column B, then:

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

Bill



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

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?

"Bill Martin -- (Remove NOSPAM from addre" wrote:

micro1330 wrote:
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.

"Bill Martin -- (Remove NOSPAM from addre" wrote:


micro1330 wrote:

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.

If your birth dates are in column B, then:

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

Bill



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

micro1330 wrote:
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?

"Bill Martin -- (Remove NOSPAM from addre" wrote:


micro1330 wrote:

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.

"Bill Martin -- (Remove NOSPAM from addre" wrote:



micro1330 wrote:


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.

If your birth dates are in column B, then:

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

Bill



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



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