• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do get a count of the rows that contain Pair

B

bines53

Active Member
Hi all,

I have a range A2; D35, and a two numbers j2, k2.

I need to find,Match multiple of two numbers.

An example in the attached file .

Thank you !
 

Attachments

Hi bines,

Is the order of two number in J2 & K2 is to be mainted in data like J2-K2-J2-K2 or it can be any order K2-J2-J2-K2?

If order has to be mainted you can use below formula.

=SUMPRODUCT(--(COUNTIF($J$2,$A$2:$A$35)+COUNTIF($K$2,$B$2:$B$35)+COUNTIF($J$2,$C$2:$C$35)+COUNTIF($K$2,$D$2:$D$35)=4))

Just advise if any issue.

Regards,
 
Hi Somendra ,

Beautiful !

I have another question ,
If there is no data like cells j2, k2,and need to find,Match multiple any combination of two numbers.

Thank you !
 
bines53 said:
Hi Somendra ,
...If there is no data like cells j2, k2,and need to find,Match multiple any combination of two numbers.
Click to expand...
So, just return a count of all the numbers? How's this:
=IFERROR((1/SUMPRODUCT(1*(A2:A35&B2:B35&C2:C35&D2:D35=J2&K2&J2&K2)))^-1,COUNT(A2:A35))
 
Hi Luke M

Another technique ,Beautiful !

I have another question ,
If there is no data like cells j2, k2,and need to find,Match multiple any combination of two numbers.

Thank you !
 
I'm not quite sure what this means:
If there is no data like cells j2, k2,and need to find,Match multiple any combination of two numbers
Click to expand...
Can you provide an example of what you would expect to see if J2 = 999, and k2 = 1000?
 
Hi Bines,

Try using below formula

=SUMPRODUCT(IF($A$2:$A$35=$C$2:$C$35,IF($B$2:$B$35=$D$2:$D$35,1,0)))

Enter with Ctrl+Shift+Enter.

Regards,
 
Here's one SUMPRODUCT without CSE
Code: 
=SUMPRODUCT(--(A2:A35/C2:C35=1),--(B2:B35/D2:D35=1))
Note: Will work with numbers only while Somendra's would work for text as well

For handling text data without having to do CSE.
Code: 
=SUMPRODUCT(--ISNUMBER(MATCH(A2:A35&B2:B35&ROW(A2:A35),C2:C35&D2:D35&ROW(C2:C35),0)))
 
Hi Bines,

See the attached file. This time couldnt come up with single cell formula. Created 3 helper cells range for three different problem using SUMPRODUCT, IF & Frequency than used COUNTIF.

Regards,
 

Attachments

I tried to combine all three conditions at one place.

In cell E2 CTRL+SHIFT+ENTER this formula and not just ENTER and then copy down:
=LOOKUP(SUM(--(A2:D2*(1/TRANSPOSE(A2:D2))=1)),{4,6,8,10,16},{"Case 1","","Case 2","Case 3",""})

Case 1 >> All 4 different
Case 2 >> 2 Pairs
Case 3 >> One Number appears 3 times
2 pink marked empty slots are:
first one is 'at least one pair' and second one is all 4 match just in case if you decide to chase them.

Once this is done then it is plain COUNTIF formula for your cases. e.g.
=COUNTIF($E$2:$E$32,"Case 1")

I'd think someone from statistical background can give you a better formula than the above one.
e.g.
Normally entered:
=MAX(COUNTIF(A2:D2,MODE(A2:D2)),1)
will give you maximum matching numbers starting from 1 (no match) to 4 (all four match).
 
You must log in or register to reply here.
Back
Top