Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
revert formula insertion to old method | Setting up and Configuration of Excel | |||
Display answer only in another cell of one containing a formula | Excel Discussion (Misc queries) | |||
Simple formula doesn't quite add up | Excel Discussion (Misc queries) | |||
Formula displays does not calculate | Excel Discussion (Misc queries) | |||
how do I make a formula NOT change when the data range is moved? | Excel Discussion (Misc queries) |