Hi
Thanks heaps. It is working really well now. Really appreciate it.
"Aladin Akyurek" wrote:
B2, copied down:
=IF(A2="","",(DATEDIF(A2,TODAY(),"Y")=3)+0)
to capture the situation that an A-cell does not house a birth date yet.
To secure the number of children older than 3 years:
=SUM(B2:B100)
To secure the number of children less than or equal to 3 years:
=COUNTIF(B2:B100,0)
Using a SumProduct formula, say in D2...
=SUMPRODUCT(ISNUMBER($A$2:$A$100)+0,(DATEDIF($A$2: $A$100,TODAY(),"Y")3)+0)
This would give you the number of children over 3 years.
D3:
=COUNTA($A$2:$A$100)-D2
would then calculate the number of children, aged less than or equal to
3 years.
Qualification: Range A2:A100 should not house any text value, including
formula blanks (i.e., "").
Sue wrote:
Hi Aladin,
I have got a problem after all. The cells to be summed where the formula
is, defaults to "1" and so I don't get the correct no. of children over 3.
My empty rows where data is yet to be input at a future date is putting out
my total figure due to all the default "1"s. I need the cells to default to
"0" where there is no date of birth details. Apart from that it works great
where the date of birth is entered in a row.
Any ideas? Thanks
"Aladin Akyurek" wrote:
A2: a date of birth
B2:
=(DATEDIF(A2,TODAY(),"Y")=3)+0
Then:
=SUM(B2:B100)
Or:
=SUMPRODUCT((DATEDIF($A@:$A$100,TODAY(),"Y")=3) +0)
Sue wrote:
How do I get excel to work out how old someone is in years as at todays date
on any given day, when I enter a date of birth into a specific column on
excel 2003. I then need the formula to recognise if it is over 3 years of
age and irrelevant of how many years past 3, that it will put a one in the
formula cell column for that row. All the 1's in that column are
subsequently summed to give me a total of all people over 3 years of age.
Currently I am entering the date of birth and then manually calculate their
age and put the 1 into the column which then gets summed.
Any ideas?
|