counting

C

chiapas77

I need help in getting excel to count the number of different number
values in a specified range of cells across a row. example:
1 1 2 should return 2
0 1 2 should return 3
2 2 2 should return 0

Can anyone help? thanks.
 
K

Kevin Vaughn

I took an array formula that I've seen used for getting unique numbers and
made a slight change to it so that if there was only one unique item, it
would return 0. Worked on your test data.

=IF(SUM(1/COUNTIF(A2:C2,A2:C2))=1,0,SUM(1/COUNTIF(A2:C2,A2:C2)))

Enter with cntl-shift-enter, not just enter.
 
K

Ken Johnson

Hi chiapas,
Please don't ask the same question on more than one forum.
People working on your problem are wasting their time if you have
already got the answer from another forum. Some of these people find
that very annoying.
Ken Johnson
 
R

Richard Buttrey

I need help in getting excel to count the number of different number
values in a specified range of cells across a row. example:
1 1 2 should return 2
0 1 2 should return 3
2 2 2 should return 0

Can anyone help? thanks.
Click to expand...

I don't quite understand why 2,2,2 should return 0. Logic suggests it
should perhaps be 1

From a suggestion by Bob Philips several months ago.

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

Just change the A:E range to cover the number of columns in your rows.

If you really want a 2,2,2 type row to show 0 rather than 1 then you
probably need to wrap it all in an If(Fn=1,0,Fn) construction.

HTH

Richard Buttrey
__
 
C

chiapas77

I apologize for the double post... Thanks Kevin and Richard for your
help... actually you are right Richard... 2,2,2 would return 1 and it's
ok. This help really got me out of a jam.

saul.
 
Top