Help!! What formula do I use?
 

I have two columns in my worksheet. In column A I have the last name and in
column B I have "active" or "inactive"

Example:
Column A Column B
Johnson Active
Johnson Inactive
Smith Active
Newsome Active
Martin Active
Martin Inactive

What I want to do is count the users who are listed as both "active" and
"inactive" once--counting them only as "active." For the above example, I
would only Johnson and Martin as "active" and ignore the listing for them
that states "inactive." (by the way, although Johnson is listed twice, it is
still the same person--just two different status)

I do not know what function or formula to use. Please help me.

Hi HL

do you want to count all of the "actives" or only count people who are
listed twice (active / inactive) as one - i'm guess i'm asking if the answer
to your example is
4
or
2

if you want to count all the actives use the COUNTIF function
=COUNTIF(B1:B6,"Active")

if its the other option i'm not sure of how to do it - maybe someone else
has an idea.

Cheers
JulieD

"HL" wrote in message
...
I have two columns in my worksheet. In column A I have the last name and
in
column B I have "active" or "inactive"

Example:
Column A Column B
Johnson Active
Johnson Inactive
Smith Active
Newsome Active
Martin Active
Martin Inactive

What I want to do is count the users who are listed as both "active" and
"inactive" once--counting them only as "active." For the above example, I
would only Johnson and Martin as "active" and ignore the listing for them
that states "inactive." (by the way, although Johnson is listed twice, it
is
still the same person--just two different status)

I do not know what function or formula to use. Please help me.

Hi
try for example for 'Johnson':
=IF(AND(SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Active")),SUMPRODU
CT(--(A1:A100="Johnson"),--(B1:B100="Inactive")),"Active","")

--
Regards
Frank Kabel
Frankfurt, Germany


HL wrote:
I have two columns in my worksheet. In column A I have the last name
and in column B I have "active" or "inactive"

Example:
Column A Column B
Johnson Active
Johnson Inactive
Smith Active
Newsome Active
Martin Active
Martin Inactive

What I want to do is count the users who are listed as both "active"
and "inactive" once--counting them only as "active." For the above
example, I would only Johnson and Martin as "active" and ignore the
listing for them that states "inactive." (by the way, although
Johnson is listed twice, it is still the same person--just two
different status)

I do not know what function or formula to use. Please help me.

Hi Frank

i knew you'ld come up with the solution - however slight modification
needed:
=IF(AND(SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Active")),SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Inactive"))),"Active","")

(three brackets after end of second sumproduct.

Cheers
JulieD

"Frank Kabel" wrote in message
...
Hi
try for example for 'Johnson':
=IF(AND(SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Active")),SUMPRODU
CT(--(A1:A100="Johnson"),--(B1:B100="Inactive")),"Active","")

--
Regards
Frank Kabel
Frankfurt, Germany


HL wrote:
I have two columns in my worksheet. In column A I have the last name
and in column B I have "active" or "inactive"

Example:
Column A Column B
Johnson Active
Johnson Inactive
Smith Active
Newsome Active
Martin Active
Martin Inactive

What I want to do is count the users who are listed as both "active"
and "inactive" once--counting them only as "active." For the above
example, I would only Johnson and Martin as "active" and ignore the
listing for them that states "inactive." (by the way, although
Johnson is listed twice, it is still the same person--just two
different status)

I do not know what function or formula to use. Please help me.

Hi Julie
good spot. Thanks for the correction :-)

--
Regards
Frank Kabel
Frankfurt, Germany


JulieD wrote:
Hi Frank

i knew you'ld come up with the solution - however slight modification
needed:

=IF(AND(SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Active")),SUMPRODU
CT(--(A1:A100="Johnson"),--(B1:B100="Inactive"))),"Active","")

(three brackets after end of second sumproduct.

Cheers
JulieD

"Frank Kabel" wrote in message
...
Hi
try for example for 'Johnson':

=IF(AND(SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Active")),SUMPRODU
CT(--(A1:A100="Johnson"),--(B1:B100="Inactive")),"Active","")

--
Regards
Frank Kabel
Frankfurt, Germany


HL wrote:
I have two columns in my worksheet. In column A I have the last
name and in column B I have "active" or "inactive"

Example:
Column A Column B
Johnson Active
Johnson Inactive
Smith Active
Newsome Active
Martin Active
Martin Inactive

What I want to do is count the users who are listed as both
"active"
and "inactive" once--counting them only as "active." For the above
example, I would only Johnson and Martin as "active" and ignore the
listing for them that states "inactive." (by the way, although
Johnson is listed twice, it is still the same person--just two
different status)

I do not know what function or formula to use. Please help me.

Thanks Frank and Julie for your help

"JulieD" wrote:

Hi Frank

i knew you'ld come up with the solution - however slight modification
needed:
=IF(AND(SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Active")),SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Inactive"))),"Active","")

(three brackets after end of second sumproduct.

Cheers
JulieD

"Frank Kabel" wrote in message
...
Hi
try for example for 'Johnson':
=IF(AND(SUMPRODUCT(--(A1:A100="Johnson"),--(B1:B100="Active")),SUMPRODU
CT(--(A1:A100="Johnson"),--(B1:B100="Inactive")),"Active","")

--
Regards
Frank Kabel
Frankfurt, Germany


HL wrote:
I have two columns in my worksheet. In column A I have the last name
and in column B I have "active" or "inactive"

Example:
Column A Column B
Johnson Active
Johnson Inactive
Smith Active
Newsome Active
Martin Active
Martin Inactive

What I want to do is count the users who are listed as both "active"
and "inactive" once--counting them only as "active." For the above
example, I would only Johnson and Martin as "active" and ignore the
listing for them that states "inactive." (by the way, although
Johnson is listed twice, it is still the same person--just two
different status)

I do not know what function or formula to use. Please help me.