I was helped with something similar a little while ago and I now have another
similar problem but this is another step up in difficulty I think.

I need to establish how many people are in 5 age bands (the last being age
not provided). However, these totals are conditional on whether the value in
column a = "text1" or "text2" but column b < "text3". Column e lists
birthdates.

--
Thanks in advance as always,
MarkN

Hi Mark again

The below will check for 'text1' and 'text2' in ColA, '<text3' in ColC and
deduct the DOB year from current year and gives the count...

'The below check for the age group =30 and <=35

=SUMPRODUCT((ISNUMBER(MATCH(A1:A10,{"text1","text2 "},0)))*(B1:B10<"text3")*(YEAR(TODAY())-YEAR(E1:E10)=30)*
(YEAR(TODAY())-YEAR(E1:E10)<=35))

If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

I was helped with something similar a little while ago and I now have another
similar problem but this is another step up in difficulty I think.

I need to establish how many people are in 5 age bands (the last being age
not provided). However, these totals are conditional on whether the value in
column a = "text1" or "text2" but column b < "text3". Column e lists
birthdates.

--
Thanks in advance as always,
MarkN

Sorry for the delayed thanks but you know how it is. Worked perfectly.
--
Thanks,
MarkN


"Jacob Skaria" wrote:

Hi Mark again

The below will check for 'text1' and 'text2' in ColA, '<text3' in ColC and
deduct the DOB year from current year and gives the count...

'The below check for the age group =30 and <=35

=SUMPRODUCT((ISNUMBER(MATCH(A1:A10,{"text1","text2 "},0)))*(B1:B10<"text3")*(YEAR(TODAY())-YEAR(E1:E10)=30)*
(YEAR(TODAY())-YEAR(E1:E10)<=35))

If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

I was helped with something similar a little while ago and I now have another
similar problem but this is another step up in difficulty I think.

I need to establish how many people are in 5 age bands (the last being age
not provided). However, these totals are conditional on whether the value in
column a = "text1" or "text2" but column b < "text3". Column e lists
birthdates.

--
Thanks in advance as always,
MarkN