Counting The Number Of Non-Duplicate Cells ??

[Aitch]

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

A	B	C	D	E	F	G
Jack	Jill	John	Jane		4
Jack	Jack	John	John		2
Jill	Jill		Jill		1
John	Jack	Jack	Jane		3

<tbody>
</tbody>

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.