Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel CHALLENGE...3 questions(indirect,rank,array formula)... | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
Running Excel events even in edit mode.... | Excel Discussion (Misc queries) | |||
How do I get the formula bar in excel 2003 | Excel Discussion (Misc queries) | |||
Suddenly Excel can't calculate formula!!! | Excel Worksheet Functions |