This looks like a job for...an ARRAY formula . . .
Posted by Chris Rock on December 04, 2001 6:56 AM
In this example, I need a COUNTIF based on a couple of different criteria:
Column A contains 2 possible values: "Unrelated" and "Related".
Column B contains Names (people's names). Some of them show up more than once.
Is it possible, using an array formula, to count the number of Unique names in the list in Column B which have "Related" in the same row in Column A? I'd like to avoid using filters to find duplicates or sorting or anything like that if possible.
If an array formula won't do it, is there any other viable solution?
Thanks!
Posted by Juan Pablo on December 04, 2001 8:52 AM
How about Pivot Talbes ? Employees on the rows, and ColumnA as column ? (NT)
Posted by Aladin Akyurek on December 06, 2001 12:36 PM
Chris --
This was a hard case to tackle with...
Lets suppose that A2:B18 houses the following sample data:
{"aaron","r";"bob","r";"bob","u";"bob","r";"bob","u";"chris","r";"chris","u";"dwight","r";"dwight","r";"dwight","u";"eric","u";"fiona","u";"gertrude","u";"gertrude","r";"han","r";"george","u";"juan","r"}
u stands for Unrelated, r for Related.
=SUM(IF(FREQUENCY(MATCH(A2:A18,A2:A18,0),MATCH(A2:A18,A2:A18,0)*(B2:B18="r"))>0,1))
will compute the desired count, that is, the count of uniques names that meet the condition "r" (Related).
Note. This is NOT an array formula (but inherently is)
Aladin
=========
.
