Adding up with array formula
 

In A1:A9 a have some values that are either numbers, texts or blanks
In B1 I have =IF(COUNTIF($A$1:A1;A1)=1;1;0) which I copy down

This formula shows 1 when a value appears for the first time
0 when the value is blank or appears for
second, third... time

Finally, I add the range B1:B9 to count the number of different values
(not blank) that my range has.

My problem is:

I want to obtain the same sum with an array formula, without
intermediate results. No matter how hard I try, I am unable to get
get.

If possible, I'd like that the array formula resembles my original
formula =IF(COUNTIF($A$1:A1;A1)=1;1;0) .

Can anybody help?

Adding up with array formula
 

So you're counting the number of unique entries in that range (a1:a9)?

=SUMPRODUCT((A1:A9<"")/COUNTIF(A1:A9,A1:A9&""))



vsoler wrote:

In A1:A9 a have some values that are either numbers, texts or blanks
In B1 I have =IF(COUNTIF($A$1:A1;A1)=1;1;0) which I copy down

This formula shows 1 when a value appears for the first time
0 when the value is blank or appears for
second, third... time

Finally, I add the range B1:B9 to count the number of different values
(not blank) that my range has.

My problem is:

I want to obtain the same sum with an array formula, without
intermediate results. No matter how hard I try, I am unable to get
get.

If possible, I'd like that the array formula resembles my original
formula =IF(COUNTIF($A$1:A1;A1)=1;1;0) .

Can anybody help?

--

Dave Peterson

Adding up with array formula
 

Try this:

=SUMPRODUCT((A1:A9<"")/COUNTIF(A1:A9,A1:A9&""))

Counts the uniques in the range.

Biff

"vsoler" wrote in message
oups.com...
In A1:A9 a have some values that are either numbers, texts or blanks
In B1 I have =IF(COUNTIF($A$1:A1;A1)=1;1;0) which I copy down

This formula shows 1 when a value appears for the first time
0 when the value is blank or appears for
second, third... time

Finally, I add the range B1:B9 to count the number of different values
(not blank) that my range has.

My problem is:

I want to obtain the same sum with an array formula, without
intermediate results. No matter how hard I try, I am unable to get
get.

If possible, I'd like that the array formula resembles my original
formula =IF(COUNTIF($A$1:A1;A1)=1;1;0) .

Can anybody help?