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!

### Like this:

Like Loading...

*Related*

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.

No need CSE

oops…. I forgot change “;” inside constant array so…

there is two minus of course before MMULT and COUNTIFS…. once again your site changes my formula ๐ฆ

@Bill SzyszSorry 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

Armeniain A3,Yerevanin B4 andDramin C5 as a positive count? Of course, it would need to beArmeniain A3,Yerevanin B3 andDramin C3.Regards

You are right… to fast answer. So i change my solution – this one will be ok…i suppose ๐

That one

doesrequire 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! ๐

@JamesCertainly not beyond your competence if you can produce formulas like that! Good stuff!@ChrisAlso 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:

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 …