Counting birthdays

T

tonystowe

In my sheet I have to include the Date of Birth (DOB) on everyone in the
list. I easily can count the number of persons entered, however I do
not know how to use the Count function to list only those 38 y/o and
older.

Anyone interested in assisting me would be greatly appreciated.

Thanks

Tony
 
A

Ardus Petus

Say DOBs are in column A:
=COUNTIF(A:A,"<"&DATE(YEAR(TODAY()-38),MONTH(TODAY()),DAY(TODAY())))

HTH
 
D

Dav

Depending on how your country considers age is 38 and 2 months 38 for
example

=COUNTIF(H17:H29,"=<26/06/68") if the dobs are in the range h17:h29

Else if you put in the cell a1
=DATE(YEAR(TODAY())-38,MONTH(TODAY()),DAY(TODAY()))

then countif(h17:h29,"<="&a1) would suffice and update daily

Regards

Dav
 
B

Bob Phillips

If you want to count those 38 and over

=SUMPRODUCT(--(DATEDIF(A2:A20,TODAY(),"Y")>=38))

If you want to list them, assuming names in A, dates in B1,

select C2:C20 (or more if more names), and in the formula bar enter

=IF(ISERROR(SMALL(IF(DATEDIF($B$2:$B$20,TODAY(),"y")>=38,ROW($A2:$A20),""),R
OW($A1:$A20))),"",
INDEX($A$1:$A$20,SMALL(IF(DATEDIF($B$2:$B$20,TODAY(),"y")>=38,ROW($A2:$A20),
""),ROW($A1:$A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
T

tonystowe

Ardus said:
Say DOBs are in column A:
=COUNTIF(A:A,"<"&DATE(YEAR(TODAY()-38),MONTH(TODAY()),DAY(TODAY())))>
Click to expand...
[/QUOTE]


Ardus,

Thanks for the formula, however as I enter names and DOB's it counts
all listed, not those who are 38 y/o and older. It appears to be
correct but I can't figure this out.

Here is what I used only changing the A:A to G2:G23:
=COUNTIF(g2:g23,"<"&DATE(YEAR(TODAY()-38),MONTH(TODAY()),DAY(TODAY())))

Any suggestions?

Tony
 
T

tonystowe

Bob said:
If you want to count those 38 and over

=SUMPRODUCT(--(DATEDIF(A2:A20,TODAY(),"Y")>=38))
Click to expand...
[/QUOTE]

Bob,

I tried this method and the formula simply gave me an answer of "20".
I have four names and DOB listed with two being 38 y/o and older and
two being younger. I was hoping to see "2" as the answer.

Does the date format of the DOB cells matter?
 
B

Bob Phillips

I should have tested for blank cells, and you should change A2:A20 to the
range where the dates are

=SUMPRODUCT(--(A2:A20<>""),--(DATEDIF(A2:A20,TODAY(),"Y")>=38))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)


Bob,

I tried this method and the formula simply gave me an answer of "20".
I have four names and DOB listed with two being 38 y/o and older and
two being younger. I was hoping to see "2" as the answer.

Does the date format of the DOB cells matter?
[/QUOTE]
 
B

Bob Phillips

bracket in the wrong place

=COUNTIF(G2:G23,"<"&DATE(YEAR(TODAY())-38,MONTH(TODAY()),DAY(TODAY())))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
T

tonystowe

Bob said:
=COUNTIF(G2:G23,"<"&DATE(YEAR(TODAY())-38,MONTH(TODAY()),DAY(TODAY())))
Click to expand...
[/QUOTE][/QUOTE]


Thanks Bob, this one worked perfect! I still am not sure as to wha
bracket was out of place or missing but its corrected now.

Thanks

Ton
 
B

Bob Phillips

The formula you were given had

TODAY()-38)

instead of

TODAY())-38

so it calculated the year of 38 days ago, instead of the year of today - 38.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

[/QUOTE]


Thanks Bob, this one worked perfect! I still am not sure as to what
bracket was out of place or missing but its corrected now.

Thanks

Tony
[/QUOTE]
 
Top