View Single Post
  #4   Report Post  
JulieD
 
Posts: n/a
Default

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.