COUNTIF........
Posted by Carl B on March 02, 2001 8:02 PM
I have a demographics worksheet labled demographics.
In column P is DOB.
In column G is Gender, denoted by M or F.
There is also a worksheet labled Counts.
How can I get a count of Males between the ages of 20 and 30 in a cell, lets say cell C2 on the Counts sheet.
Posted by Aladin Akyurek on March 03, 2001 12:40 AM
Hi Carl
Array-enter (d.i. hit CONTROL+SHIFT+ENTER to enter) the following formula in C2 on the Counts sheet.
=SUM((YEAR(TODAY())-YEAR(BDATES)>=20)*((YEAR(TODAY())-YEAR(BDATES)<=30))*(GENDER="M")),
where BDATES (or DOB) and GENDER are the names given to the ranges of the birth dates and gender via the Name Box (or via the option Insert|Name|Define).
Adjust the part that computes the age if you want more precision with ages.
Aladin
Posted by Carl B on March 03, 2001 12:45 AM
Re: SUM() ??
How is SUM() getting a count of.
Thought COUNTIF was the counter.
Ecxuse my ignorance here.
Posted by Aladin Akyurek on March 03, 2001 1:02 AM
Re: SUM() ??
COUNTIF cannot handle multiple conditions. Whence an array formula (for your situation) that is built with so-called Boolean expressions which when evaluated result in TRUE and/or FALSE values, put differently in 1 and/or 0 values. The formula produces a single array of 1's and 0's after the multiplication of 3 arrays of 1's and 0's. SUM sums the 1's to give you a count.
Aladin
Posted by Carl B on March 03, 2001 4:28 AM
That makes sense §:^) Thanks Aladin
