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

AndyJR

Board Regular
Joined
Jun 20, 2015
Messages
90
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!!
 
Last edited by a moderator:

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Andy. This is a bit cumbersome looking but if I understand correctly, it may set you an the right path...


Excel 2010
ABCDEFG
1142364
251789
311431015
414121312
BUM7
Cell Formulas
RangeFormula
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.
 
Upvote 0
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)

[TABLE="width: 219"]
<colgroup><col span="5"><col></colgroup><tbody>[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD] 2 [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD] 1[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]11[/TD]
[TD]1[/TD]
[TD]10[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]10[/TD]
[TD]23[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
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)))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top