Formula to Count Matched reference as 1, regardless if are multiples matched in Range/Reference.

[AndyJR]

Good Morning,
I don't see easy, is a bit complicated my goal


I'm struggling with a formula to count the matched reference on a Range to be matched.
I know that are a some some formulas that do that job, but what i need to do is go a lil beyond the count.

I applied the formulas , Count, Countif, Sumproduct w countif, sumproduct w match, Sum if frequency w match,
Even, i nested 5 countif formulas.
The Count is correct as long if both range have no duplicated (reference VS Range)
BUT once it is a duplicated value on Range or References, the count is not what i need.

Note: if reference has duplicated, each duplicated need to be counted as 1, Even if in the Range is more 2 or more times.
Example



https://www.dropbox.com/s/qp93l86lc05wzx0/CountSample.png?dl=0
Thank you!!

 

[Snakehips]

Andy. This is a bit cumbersome looking but if I understand correctly, it may set you an the right path...

Excel 2010
	A	B	C	D	E	F	G
1	1	4	2	3	6		4
2	5	1	7	8	9
3	11	4	3	10	15
4	14	12	13	1	2
BUM7

Cell Formulas
Range	Formula
G1	=(COUNTIF(A2:E4,A1)>=1)/COUNTIF(A1:E1,A1)+ (COUNTIF(A2:E4,B1)>=1)/COUNTIF(A1:E1,B1)+(COUNTIF(A2:E4,C1)>=1)/COUNTIF(A1:E1,C1)+(COUNTIF(A2:E4,D1)>=1)/COUNTIF(A1:E1,D1)+(COUNTIF(A2:E4,E1)>=1)/COUNTIF(A1:E1,E1)

Hope it helps.

 

[AndyJR]

Hi Mr Snakehips,

The formula work good by a 95% , but still time to time it got wrong count by minus 1

example:The formula count 1, but is supposed to count 2
Because reference A1:O1 the #1 is 2 times, and on A2:O4 it appear #1 , It doesn't matter if on A2:O4 #1 appear 2+ times or just 1 time.
On reference A1:O1 is where the count have to be 1 time for each (if exist at least 1 time on A2:O4)

1	4	1	3	2	2
1	5	5	5	5	1
12	11	1	10	5
12	5	6	10	23

<colgroup><col span="5"><col></colgroup><tbody>
</tbody>

Let say on above example:
If we let A2:O4 The same way as appear now BUT on A1:O1 #1 appear just 1 time , Then the total count is 1.

I Know Mr NsnakeHips, is a lil bit difficult, i can't even mention my problem with formula, because i don't really know.


Thanks for your kindness.

 

[Eric W]

I must say I'm having a devil of a time trying to follow the explanations, but this formula seems to work for your examples:

G1: =SUMPRODUCT(SIGN(COUNTIF(A2:E4,A1:E1)))

 

[AndyJR]

Hi Mr Eric W,

You make me laugh, that even my wife got scared.. jajajajajja

Yes i know, I know what i need but i can't i can't find a short and concise explanation,
I'm sorry (For real i'm sorry, but still i'm laughing...)


I'll Try on at least 20 ranges, Let you know


Thank you!!!!

Andy

 

[AndyJR]

Hi Eric W

Yes sir, corretc, The sign tingui made the difference, i have to look at it.

Thank you so much Mr Eric!!

also many thanks to snakehip!!


Andy

 

[AndyJR]

[thread solved]

 

[Eric W]

Glad we could help!