How to capture number of kids whose under 20 year old?

[Shermaine2010]

Hi, please help
And look into file attachment

[mrice]

Try this

Type Leo in D2
Type PL in D3

Paste into E2
=COUNTIFS(A:A,D2,B:B,">"&DATEVALUE(DAY(NOW())&"/"&MONTH(NOW())&"/" &YEAR(NOW())-20))

Copy E2 to E3

Note that the dates need to be recognized as dates. Suggest using a / instead of a .

[FDibbins]

mrice, OP's profile (and .xls file upload) indicates 2003. If thats the case, countifS() wont work for them

edit: also, the dates are not dates, but text that looks like dates

edit2:
In C2, copied down, use this to convert to dates...
=SUBSTITUTE(B2,".","/")*1
then for leo use this...
=SUMPRODUCT(($A$2:$A$9="Leo")*($C$2:$C$9>TODAY()-DATE(20,0,0)))
and for PL use this...
=SUMPRODUCT(($A$2:$A$9="PL")*($C$2:$C$9>TODAY()-DATE(20,0,0)))

[benishiryo]

hi Shermaine2010, an alternative considering that you are using Excel 2003 would be:
=SUMPRODUCT((A2:A9="Leo")*(DATEDIF(DATE(RIGHT(B2:B9,4),MID(B2:B9,4,2),LEFT(B2:B9,2)),TODAY(),"y")<20))
that's for leo

and for PL, just change the one in red:
=SUMPRODUCT((A2:A9="PL")*(DATEDIF(DATE(RIGHT(B2:B9,4),MID(B2:B9,4,2),LEFT(B2:B9,2)),TODAY(),"y")<20))

you can also refer to a cell reference like what mrice has suggested. do note that Leo actually has 4 children under 20

[Kevin UK]

Hi

Another version. In cell C2= Leo & cell C3 = PL. Then in D2 and copy to D3.

Please Login or Register  to view this content.

[Shermaine2010]

hi Shermaine2010, an alternative considering that you are using Excel 2003 would be:
=SUMPRODUCT((A2:A9="Leo")*(DATEDIF(DATE(RIGHT(B2:B9,4),MID(B2:B9,4,2),LEFT(B2:B9,2)),TODAY(),"y")<20))
that's for leo

and for PL, just change the one in red:
=SUMPRODUCT((A2:A9="PL")*(DATEDIF(DATE(RIGHT(B2:B9,4),MID(B2:B9,4,2),LEFT(B2:B9,2)),TODAY(),"y")<20))

you can also refer to a cell reference like what mrice has suggested. do note that Leo actually has 4 children under 20

benishiryo : I get your mean.Another problem is i have unknow number for the list (A2:A9). Therefore, I only can assume the list up to 5000 : A1:A500. Result appears as error. Help!

[Shermaine2010]

Yes, is text format.
I would like to use Year only to count the year old. any suggestion?

[FDibbins]

If you only want to compare years, in C2, copied down...
=VALUE(RIGHT(B2,4))

then for Leo use...
=SUMPRODUCT(($A$2:$A$5000="Leo")*($C$2:$C$5000>VALUE(TEXT(TODAY()-DATE(20,0,0),"YYYY"))))
and for PL use...
=SUMPRODUCT(($A$2:$A$5000="PL")*($C$2:$C$5000>VALUE(TEXT(TODAY()-DATE(20,0,0),"YYYY"))))

if you have more than 5000 rows, adjust the 5000 in both cases (see bolded)

[icestationzbra]

in the context of the sample workbook that you uploaded in post #1, following single formula will beget 4 for Leo and 2 for PL:

Please Login or Register  to view this content.

[Shermaine2010]

'- ----------

[Shermaine2010]

If you only want to compare years, in C2, copied down...
=VALUE(RIGHT(B2,4))

then for Leo use...
=SUMPRODUCT(($A$2:$A$5000="Leo")*($C$2:$C$5000>VALUE(TEXT(TODAY()-DATE(20,0,0),"YYYY"))))
and for PL use...
=SUMPRODUCT(($A$2:$A$5000="PL")*($C$2:$C$5000>VALUE(TEXT(TODAY()-DATE(20,0,0),"YYYY"))))

if you have more than 5000 rows, adjust the 5000 in both cases (see bolded)

Managed found the reason. I have have 2000 rows but 1000-2000 is blank data. Therefore formula below show error.
SUMPRODUCT(( 'Adhoc-famil'!$A$2:$A$16=B19)*((Calender!$G$1-RIGHT('Adhoc-famil'!$F$2:$F$16,4)<21)))

What formula i can apply for?

[FDibbins]

where did the (Calender!$G$1-RIGHT('Adhoc-famil'!$F$2:$F$16,4)<21 come from??