Assuming the first two lines are in rows 1 and 2, in A3:
=1*(COUNTIF(1:1,A2)>=COUNTIF($A2:A2,A2))
Copy to the right as needed. Then total count is:
=SUM(3:3)
I appreciate the desire to help
If easier to work only on numbers, there is no problem with it
Can I convert numbers to letters, even if there is a need to work with lots of rows.
Hi bines53,
Responding to your message, I'm not sure what you meant by "combine all 6 options". The orginal formula setup I posted should work with both numbers and text, so there is no issue there. Is there something else you need/wanted the formulas to do?
@Debraj
One problem I see with your formula is that is line 2 has more entries of a number than line 1, the formula counts all of them. From the example, if there were 2 7's in the top, and 3 7's in the bottom, the answer should only be 2.
I think the challenge is trying to figure out how to limit the matches.
Change D3 from 7 to A. This should cause the count to go down to 3 (since we only have 1 A in the top row), but formula gives us 4.
Please don't think I'm picking on you Deb, I'm as stumped as you are. This is certainly an interesting challenge. Feels like it should be easy, but can't figure it out!
I don't think, in this scneario, the numbers vs. text is a problem. I admit I don't know how to make XL only look at part of a range for an array. I tried doing:
INDEX(A2:F2,COLUMN(A2:F2))
hoping that the COLUMN function would generate an array, and then cause the INDEX to give me 6 differently sized arrays, but alas, it does not. Thinking back to the XL challenges forum, perhaps @Haseeb A or @jeffreyweir or @Hui can think up of something. I do not fully understand the "dark arts" or using MMULT and FREQUENCY, perhaps there is a way with those function? I'm sorry I can't be of more help.