Need explanation on Count no repeat with conditions

Hi.

Let's reduce the last row to 10 for the purpose of aiding the explanation. Let's also assume that H28 contains "X" and I27 contains "Y", and that we have the following data in A2:D10:

	A	B	C	D
2	G	Y	X	X
3	C	Y	X
4	C	Y
5	C
6	A	Y		X
7	C	Y	X	X
8	I			X
9	G	Y	X
10	A		X	X

The first condition, i.e.:

IF($C$2:$D$10=$H28

tests whether there is at least one "X" in columns C and D for rows 2 to 10. Actually, this construction is quite advanced, and not many people would be aware that it is doing just that: the "usual" construction here for testing whether there is an "X" in either column C or column D would be to form two separate tests and sum them, i.e.:

IF(($C$2:$C$10=$H28)+($D$2:$D$10=$H28)

though your construction, if less intuitive, is certainly better, and especially if we were to extend this "OR" criterion to more than just two columns, the difference between e.g.:

IF($C$2:$G$10=$H28

and:

IF(($C$2:$C$10=$H28)+($D$2:$D$10=$H28)+($E$2:$E$10=$H28)+($F$2:$F$10=$H28)+($G$2:$G$10=$H28)

being quite noticeable.

It should, however, be pointed out that you are only able to use this abbreviated version due to the particular circumstances (i.e. passing the resulting array to FREQUENCY) and that, in general, an attempt at such a shortcut would lead to erroneous results.

Using the data provided:

IF($C$2:$D$10=$H28

resolves to:

IF({TRUE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,TRUE;TRUE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,TRUE}

Similarly, the second clause, i.e.:

IF($B$2:$B$10=$I$27

resolves to:

IF({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}

The next portion:

MATCH($A$2:$A$10,$A$2:$A$10,0)

generates an array consisting of the relative positions of the first occurrence of each of the values within A2:A10 within that same range, i.e.:

{1;2;2;2;5;2;7;1;5}

Putting these results together, we see that:

IF($C$2:$D$10=$H28,IF($B$2:$B$10=$I$27,MATCH($A$2:$A$10,$A$2:$A$10,0)))

which is:

IF({TRUE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,TRUE;TRUE,TRUE;FALSE,TRUE;TRUE,FALSE;TRUE,TRUE},IF({TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE},{1;2;2;2;5;2;7;1;5}))

resolves to:

{1,1;2,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,5;2,2;FALSE,FALSE;1,FALSE;FALSE,FALSE}

and you can see that the non-FALSE entries in this array are precisely the row numbers - 1, 2 and 5 - for which the entry in column B is "Y" and either the entry in column C is "X" or the entry in column D is "X".

Of course, these entries are duplicated within this array, and that's where FREQUENCY comes in use.

The construction for generating FREQUENCY's bins_array is a very standard one, viz:

ROW($A$2:$A$10)-ROW($A$2)+1

and is used to generate an array of integers from 1 up to the number of rows in our range (A2:A10), i.e. 9.

It resolves as:

{2;3;4;5;6;7;8;9;10}-2+1

i.e.:

{1;2;3;4;5;6;7;8;9}

So we now have:

FREQUENCY({1,1;2,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,5;2,2;FALSE,FALSE;1,FALSE;FALSE,FALSE},{1;2;3;4;5;6;7;8;9}

and, since FREQUENCY operates by allocating each of the values in the first array to one of the "bins" within the second, so that, for example, the 3 1s in the first array will be allocated to the first bin, i.e. 1, the 3 2s will be allocated to the second bin, i.e. 2, the above is:

{3;3;0;0;1;0;0;0;0;0}

and you can see that the use of FREQUENCY in this way, by grouping similar values into bins, has effectively given us a means to count the number of unique entries matching our criteria.

Finally, then, we calculate:

SUM(IF({3;3;0;0;1;0;0;0;0;0}),1))

which is:

SUM({1;1;FALSE;FALSE;1;FALSE;FALSE;FALSE;FALSE;FALSE}))

i.e. 3, as required.

Hope that helps.

Regards

You're welcome!