Advanced Countif (?) Formula
 

I have an excel spreadsheet in which I am trying to calculate: for example:

1. the number of males or females that have health insurance
2. the average age of the males or females that have health insurance and
the averge tenure of the males or females that have health insurance. In
other words -

The spreadsheet contains a list by row of males and females (a census of
employees at a compnay) hat contain age, gender, date of hire and health
insurance status.

Examples of the columns and fields:

Gender Date of Birth Date of Hire Health
Status
M 08/12/70 05/01/07 EE
F 08/12/70 05/01/07 FAM
F 03/12/70 04/01/06 DEC


I need to be able to, by a formula, determine answers for only those males
(one answer) or females (a different answer) who have health insurance.

fields/column headers of data:

gender: the fields in this column contain the data 'm' for male and 'f'
for female

health insurance: the field in this column contains data - either 'ee' or
'fam' for those males/females in the list that have 'ee' or 'fam' health
insurance; if the male does not have health insurance the field in this
column contains data - either 'dec' or 'oc'

age: the fields in this column both contain 'date' data for all males in
the list

tenu the fields in this column both contain 'date' data for all males in
the list

Advanced Countif (?) Formula
 

Number of males

=COUNTIF(A:A,"M")

Averages of males with health insurance

=SUMPRODUCT(--(A2:A20="M"),--((D2:D20="EE")+(D2:D20="FAM")))/SUMPRODUCT(--(A2:A20={"M","F"}))

Average tenure

=SUMPRODUCT(--(A2:A20="M"),--((D2:D20="EE")+(D2:D20="FAM")),(C2:C20-B2:B20))/COUNTIF(A:A,"M")

--
__________________________________
HTH

Bob

"Glendae" wrote in message
...
I have an excel spreadsheet in which I am trying to calculate: for example:

1. the number of males or females that have health insurance
2. the average age of the males or females that have health insurance and
the averge tenure of the males or females that have health insurance. In
other words -

The spreadsheet contains a list by row of males and females (a census of
employees at a compnay) hat contain age, gender, date of hire and health
insurance status.

Examples of the columns and fields:

Gender Date of Birth Date of Hire
Health
Status
M 08/12/70 05/01/07 EE
F 08/12/70 05/01/07
FAM
F 03/12/70 04/01/06
DEC


I need to be able to, by a formula, determine answers for only those males
(one answer) or females (a different answer) who have health insurance.

fields/column headers of data:

gender: the fields in this column contain the data 'm' for male and 'f'
for female

health insurance: the field in this column contains data - either 'ee' or
'fam' for those males/females in the list that have 'ee' or 'fam' health
insurance; if the male does not have health insurance the field in this
column contains data - either 'dec' or 'oc'

age: the fields in this column both contain 'date' data for all males in
the list

tenu the fields in this column both contain 'date' data for all males
in
the list