Counting The Number Of Non-Duplicate Cells ??

[Aitch]

What is the easiest way to count cells with the same value, across columns?

[TABLE="class: grid, width: 100, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Jill[/TD]
[TD]John[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Jack[/TD]
[TD]John[/TD]
[TD]John[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD]Jill[/TD]
[TD][/TD]
[TD]Jill[/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]Jack[/TD]
[TD]Jack[/TD]
[TD]Jane[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


The first 4 columns have names as above, and column F has the number of non-duplicates - I hope I'm explaining it okay!

Thanks for the help!

 

[jmacleary]

Hi there, you can do this with a formula like

=SUMPRODUCT(1/COUNTIF(data,data&""))

In your example if it starts in A1, then the formula in F3 would be: =SUMPRODUCT(1/COUNTIF(A3:D3,A3:D3&""))


NB I didn't create this formula - I have found it in the past and saved it for future use - I cant remember now where it came from.

 

[Aladin Akyurek]

In F2 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(A2:D2<>"",MATCH(A2:D2,A2:D2,0)),COLUMN(A2:D2)-COLUMN(A2)+1),1))

which is a tad faster option.