The table on the left shows the results of a test in which a list of countries was provided (in A3:A12) and the student asked to list both the capital city and currency for each of those countries.
Not all of these answers are correct, as can be verified using the table on the right.
The challenge this week is as follows: a single formula in C1 to give the number of cases in which the student correctly identified both the capital city and the currency. The answer for this student would be 4, as highlighted.
Note that this is a shortest formula challenge, which means that readers should attempt to find not only a correct solution to the problem but also one which has the least number of characters as possible.
Any ranges must include both a row and column reference: A:C or 3:12, for example, are not acceptable. Named Ranges are also not permitted.
You can download the workbook here.
Solution next week. Best of luck!
Hi again XOR LX!
The formula could be (Ctrl + Shift + Enter):
Blessings!
Ok, I have my solution. It is 58 characters, not counting the curly brackets. I will post it later after I think some more. So what length are you other guys coming up with?
Well there is no sense in keeping mine to myself now as john copied my paper :). However John I think you will need the 0 for exact match to get the proper answer.
Hi GreasySpot!
You can omit the 0 if you put a comma (argument separator) “,” at the end of match. This trick works also in Vlookup for example. My “match” does an exact match. Try yourself. Blessings!
Sweet, I didn’t know it worked that way.
=SUM(--(MMULT(--(COUNTIFS(A3:C12,E3:G12)>0),{1;1;1})=3))No need CSE
oops…. I forgot change “;” inside constant array so…
=SUM(--(MMULT(--(COUNTIFS(A3:C12,E3:G12)>0),{1,1,1})=3))there is two minus of course before MMULT and COUNTIFS…. once again your site changes my formula 😦
@Bill Szysz
Sorry about that editor! All fixed.
Actually, I think your syntax was correct with the first version.
And are you absolutely sure about your logic? Is that result not just a coincidence?
I mean – does your solution factor in that the entries have to occur within the same row? For example, would your formula not register a case such as Armenia in A3, Yerevan in B4 and Dram in C5 as a positive count? Of course, it would need to be Armenia in A3, Yerevan in B3 and Dram in C3.
Regards
You are right… to fast answer. So i change my solution – this one will be ok…i suppose 🙂
That one does require CSE.
Regards
This is way beyond my competence …
But for the fun of It …
Certainly the shortest I can come up with is:
Hi James/Chris and welcome to both of you to the site! 🙂
@James Certainly not beyond your competence if you can produce formulas like that! Good stuff!
@Chris Also very good! But aren’t you missing a final parenthesis there? I think this will be identical to that given earlier by John Jairo V.
Cheers
For completeness, I actually started with (73 Chars):
and worked my way down to 55 chars above as per a number of others.
Thinking about it, I think you could make it fractionally shorted using FIND instead of MATCH but I am not sure how to concatenate the array properly:
The below works though:
Here is what I have so far in Google Sheets:
Formula:
=rows({A3:C12;E3:G12})-rows(unique({A3:C12;E3:G12}))Length: 51 characters
The formula takes the # of rows in the vertically merged range and subtracts the number of unique rows in the same range. The UNIQUE function removes duplicate rows (whether the array is one or more columns). In this case, it’s 20-16 = 4. The difference is due to having the same row in both ranges, meaning a row of answers is correct.
Link to spreadsheet: https://docs.google.com/spreadsheets/d/1DPu928lTdAltz64gkkm03WWSmpvSGFEtZE8XbLpY310/edit?usp=sharing
Also, could someone explain to me what CSE is?
@ Isai
CSE = Control+Shift+Enter …