Counting unique values in multiple columns

[Robert AH Prins]

I'm struggling to find a way to count unique values in two non-adjacent columns,
further restricted by the fact that a third column has to have another value. So
for the table below, where the unique values to be counted are in cols 2 & 4 and
the discriminant is in col 1,

1 a col-3 a
1 b col-3 b
1 b col-3 b
1 b col-3 c
1 c col-3 c
1 c col-3 d
2 a col-3 a
2 a col-3 b
2 b col-3 b
2 c col-3 c

the result for 1 should be that there are 4 unique values (a/b/c/d), the result
for 2 should be 3 (a/b/c).

I've come across plenty of "SUMPRODUCT(1/COUNTIF(B1:B10,B1:B10))" type pages,
but doing this over two non-adjacent columns further discriminated by a third,
I'm stuck.

Robert

 

[Claus Busch]

Hi Robert,

Am Wed, 31 Jul 2013 01:28:37 +0000 schrieb Robert AH Prins:

1 a col-3 a
1 b col-3 b
1 b col-3 b
1 b col-3 c
1 c col-3 c
1 c col-3 d
2 a col-3 a
2 a col-3 b
2 b col-3 b
2 c col-3 c

the result for 1 should be that there are 4 unique values (a/b/c/d), the result
for 2 should be 3 (a/b/c).

for 1 try:
=SUMPRODUCT((MATCH(A1:A10&D110,A1:A10&D110,0)=ROW(1:10)*(A1:A10=1)*(D110<>""))*1)


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Wed, 7 Aug 2013 20:34:48 -0700 (PDT) schrieb
(e-mail address removed):

Claus, I don't know if you tried your formula, but it doesn't work. There are a number of reasons:

everything I post is tested and works


Regards
Claus B.

 

[Robert Prins]

Am Wed, 31 Jul 2013 01:28:37 +0000 schrieb Robert AH Prins:



for 1 try:

=SUMPRODUCT((MATCH(A1:A10&D110,A1:A10&D110,0)=ROW(1:10)*(A1:A10=1)*(D110<>""))*1)

Care to explain how this actually works given that it does not even reference Column B?

FWIW, I've temporarily added a column to the sheet with the following formula, and assuming that the dates are in cols C & F, and put the formula in column "W"

=IF(C1=C2,0,1)+IF(AND(C1<>F1,F1<>C2),F1-C1,IF(AND(C1<>F1,F1=C2),F1-C1-1,0))

and sum that (for the appropriate discriminant with a "=SUMIF(Data!A1:A2998,Calcs!A1,Data!W1:W2998)"), the totals will be OK.

Now the next question is, is this re-creatable without using a new column?

As for my formula:

The first "IF" statement: if the dates on two consecutive rows in the first column are the same, we do not want to count the first of those two dates.

The second "IF" statement: if the two dates in a single row are the same, AND the second date is not equal to the first date in the next row, then the number of unique dates is difference between the two same-row dates, unless and that is

The third "IF" statement, the first date on the next row is equal to the second date on the first row, in which case we need the above difference minus 1.

This works out for all 124 sections of the "data" sheet, maybe it can be coded shorter, but this way I will still understand it in a years time.

 

[Claus Busch]

Hi Robert,

Am Wed, 21 Aug 2013 07:56:11 -0700 (PDT) schrieb Robert Prins:

=IF(C1=C2,0,1)+IF(AND(C1<>F1,F1<>C2),F1-C1,IF(AND(C1<>F1,F1=C2),F1-C1-1,0))

try:
=SUMPRODUCT((MATCH(C1:C100&D1100&F1:F100,C1:C100&D1100&F1:F100,0)=ROW(1:100)*(C1:C100=1))*1)-1


Regards
Claus B.