| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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) | |||
Hybrid Mode
