Looking for an easy formula to find doubles and count X's

[MrsFrankieH]

Hello members,

I'm using Excel 10 and my operating system is Windows 7.
Could someone please help me with a formula for my spreadsheet on Excel.

The first part of the formula would find "singles, doubles, triples, and quadruples" from a line of four numbers. When the oppropriate numbers are found, an X will be displayed in the oppropriate box.

The second part would count the X's and put the total in the oppropriate box.

Thank you for your help.

Frankie <a href='http://<a href="http://s213.beta.photobucket.com/user/FHands/media/mrexcelss_zpsdd953106.jpg.html" target="_blank"><img src="http://i213.photobucket.com/albums/cc188/FHands/mrexcelss_zpsdd953106.jpg" border="0" alt="Photobucket"/></a>' target="_blank">http://

 

[rconverse]

For the count you can use the below in cell F37.

=countif(F6:F35, "X")

How are you recognizing singles, doubles, triples, & quadruples?

 

[circledchicken]

Hi Frankie,

In F6 try the following array formula and drag down to F35 (note that you will need to press CTRL-SHIFT-ENTER rather than just ENTER as this is an array formula):

=IF(MAX(COUNTIF(A6:D6,A6:D6))=1,"x","")

Use the same formula in H6, J6 and L6 changing the 1 in bold above to 2,3, and 4 respectively.

rconverse has suggested the formula for counting the x's.

 

[MrsFrankieH]

For the count you can use the below in cell F37.

=countif(F6:F35, "X")

How are you recognizing singles, doubles, triples, & quadruples?

Hello rconverse!

Thank you for the formula! I really appreciate it!


The way i find "singles, doubles, triples, and quadruples" is by looking at the draw. For example: If the draw has four individual unique numbers, it's "singles" (0123). If the draw has two unique numbers and two duplicate numbers, it's "doubles (0133). If the draw has one unique number and three duplicate numbers, it's "triples"(1333) and if the draw has four identical numbers, it's "quadruple (3333).
Sometimes, the doubles in the draw will have two doubles (1133).

I'm going to apply the formula now. Thank you so much for your help.

Frankie

 

[MrsFrankieH]

Hello circledchicken!!

Thank you for the formula! It looks really easy and i'm sure it will work!

I really appreciate your help!!

Thanks again!

Frankie

 

[circledchicken]

You're welcome! - thanks for the feedback.

 

[MrsFrankieH]

For the count you can use the below in cell F37.

=countif(F6:F35, "X")

How are you recognizing singles, doubles, triples, & quadruples?

Hello again rconverse!!

I was unsuccessful in getting the formula to work. I tried several times. Would you have another formula in your arsenal for this function?

Thank you again!!

Frankie