Countif within range but ignore duplicates in row

[sharky12345]

I need a formula that will count the amount of times a value appears in a range, ("A4:K5000"), but I need it to only count the value once if it appears more than once in each row.

As an example, if the value appeared 3 times in row 5 and once in row 6 then the result I would expect would be 2.

This formula does what I need but ignores duplicates in columns rather than rows - can it be tweaked?

[B]=SUMPRODUCT((A2:A15<>"")/COUNTIF(A2:A15,A2:A15&""))[/B]

 

[Fluff]

You're formula does not match what you say you want.
Can you provide some sample data along with expected result.

 

[sharky12345]

Sorry Fluff - here's a sample:

Input v6.xlsm
	F	G	H	I	J	K
4	20177 UNDKIN	23975 KRBUSE	25088 LORTAY
5	25604 LERBUT	24312 YIBNNY
6	24312 YIBNNY	25604 LERBUT	24242 LYDOE	22009 KERBA
7	25088 LORTAY	3032 WOODER	24242 LYDOE	21015 TONCHAR
8	21015 TONCHAR	22009 KERBA	1190 PSTME
9	23975 KRBUSE	3552 TNSKIG
10	203 GALN	203 GALN
11	24273 LAIGE	22643 LJONS	3032 WOODER
12	22009 KERBA	25604 LERBUT
13	24242 LYDOE	24242 LYDOE
14	20177 UNDKIN	24242 LYDOE
15	22643 LJONS	24312 YIBNNY	1190 PSTME	3032 WOODER
16	3552 TNSKIG	3032 WOODER	24273 LAIGE
17	22643 LJONS	3032 WOODER	24273 LAIGE	2417 MBLU
18	22643 LJONS	2417 MBLU
19	24273 LAIGE	203 GALN
20	24312 YIBNNY	24273 LAIGE	25604 LERBUT	20354 TEEVDMC
21	25088 LORTAY	25088 LORTAY	25604 LERBUT
22	24242 LYDOE	22643 LJONS	25604 LERBUT
23	21719 EECRESA	25088 LORTAY
24	25604 LERBUT	3032 WOODER
25	24312 YIBNNY	23975 KRBUSE	22009 KERBA
26	20354 TEEVDMC	3032 WOODER
27	25604 LERBUT	24312 YIBNNY	3552 TNSKIG
28	21719 EECRESA	22009 KERBA	20177 UNDKIN
29	22009 KERBA	203 GALN	25604 LERBUT
30	25088 LORTAY	3032 WOODER	25604 LERBUT
31	20177 UNDKIN	23975 KRBUSE
32	22643 LJONS	22643 LJONS
33	2833 DORFLO	21719 EECRESA
34	24273 LAIGE	2833 DORFLO
35	22009 KERBA	3032 WOODER	2417 MBLU
36	23975 KRBUSE	24273 LAIGE	20354 TEEVDMC
CHECKS (2)

If I was looking for 203 GALN I would expect a result of 3 - it appears twice in row 10 but I only want it counted once per row.

Does this help?

 

[Fluff]

Ok, thanks for that, try

=SUMPRODUCT(--(MMULT(--(A4:K5000="203 galn"),ROW(1:11)^0)>0))

 

[sharky12345]

You are a star - thanks Fluff, and Happy New Year!

 

[Fluff]

You're welcome & thanks for the feedback.

Happy New Year to you as well.