No. in age range if age in years and months

**13sydwest** · 08-27-2011, 11:19 PM

Stumped on how to count people in group 1 with age 4y 0m or more but less than 18y 0m.

(4x8 worksheet attached with dates in format dd/mm/yy)

**jeffreybrown** · 08-27-2011, 11:40 PM

Can you use a helper cell...

Add a column D for just the year

In column D...D2 copied down =DATEDIF(B2,A2,"Y")

=COUNTIFS($E$2:$E$7,1,$D$2:$D$7,">=4",$D$2:$D$7,"<=18")

**13sydwest** · 08-28-2011, 02:22 AM

Thanks Jeff. Problem is that the helper cell (if I've understood it correctly) returns any age between 18y 0m and 18y 11m as "18".

Say I want no. in group with age equal to or greater than 4 but less than or equal to 18.

I've added a highlighted row to the worksheet where age of person is 18y 4m. Helper cell then shows this as 18. The COUNTIFS function includes this person in the age range requested and so returns an incorrect count.

I guess I could get the helper cell to return age in months, or even better, in weeks to get a more accurate, yet possibly still incorrect, count.

Syd

**ConneXionLost** · 08-28-2011, 02:44 AM

From your first sample spreadsheet, and without a helper column, you can use SUMPRODUCT like this:

=SUMPRODUCT(--(D2:D8=1),--(A2:A8>=DATE(YEAR(B2:B8)+4,MONTH(B2:B8),DAY(B2:B8))),--(A2:A8<DATE(YEAR(B2:B8)+18,MONTH(B2:B8),DAY(B2:B8))))

or COUNTIFS like this:

=COUNTIFS(D2:D8,1,A2:A8,">="&DATE(YEAR(B2:B8)+4,MONTH(B2:B8),DAY(B2:B8)),A2:A8,"<"&DATE(YEAR(B2:B8)+18,MONTH(B2:B8),DAY(B2:B8)))

but the COUNTIFS will require Ctrl-Shift-Enter.

**13sydwest** · 08-28-2011, 03:22 AM

Many thanks. The SUMPRODUCT will be perfect for me.
I tried the COUNTIFS (with the CTRL+SHIFT+ENTER) but could only get it to return "0" instead of 2. Not sure how I've applied it wrongly.
Syd