Conditional age grouping
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
|