Remember Me?

#1
October 27th 04, 05:13 PM
 HL Posts: n/a
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)

#2
October 27th 04, 05:21 PM
 JulieD Posts: n/a

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
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)

#3
October 27th 04, 05:25 PM
 Frank Kabel Posts: n/a

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)

#4
October 27th 04, 05:30 PM
 JulieD Posts: n/a

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)

#5
October 27th 04, 05:36 PM
 Frank Kabel Posts: n/a

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)

#6
October 27th 04, 06:45 PM
 HL Posts: n/a

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)

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Don't be a pain in the ass Setting up and Configuration of Excel 0 January 24th 05 01:49 PM Mally Excel Discussion (Misc queries) 5 January 21st 05 01:07 PM circeo Excel Discussion (Misc queries) 3 January 17th 05 09:04 PM Neil Bhandar Excel Discussion (Misc queries) 4 January 10th 05 10:55 PM Alida Andrews Excel Discussion (Misc queries) 2 January 6th 05 09:02 PM

All times are GMT +1. The time now is 05:26 AM.