Shortest Formula Challenge #2: Capitals and Currencies 18

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.

Capitals and Currencies

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!

18 comments

  1. Hi again XOR LX!

    The formula could be (Ctrl + Shift + Enter):

    =COUNT(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G12,))

    Blessings!

  2. 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?

  3. 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.

    =COUNT(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G12,0))
  4. 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!

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

    =SUM(--(MMULT(--(COUNTIFS(A3:C12,E3:G12)>0),{1,1,1})=3))
  6. there is two minus of course before MMULT and COUNTIFS…. once again your site changes my formula ๐Ÿ˜ฆ

  7. @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

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

    =SUM(COUNTIFS(A3:A12,E3:E12,B3:B12,F3:F12,C3:C12,G3:G12))
  9. This is way beyond my competence …

    But for the fun of It …

    =SUM(IF(ISERROR(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G12,0)),0,1))
  10. Certainly the shortest I can come up with is:

    =COUNT(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G12,)
  11. 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

  12. For completeness, I actually started with (73 Chars):

    =SUM(IF(ISNUMBER(MATCH(A3:A12&B3:B12&C3:C12,E3:E12&F3:F12&G3:G13,0)),1,0))

    and worked my way down to 55 chars above as per a number of others.

  13. 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:

    =COUNT(FIND(A3:A12&B3:B12&C3:C12,E3&F3&G3&E4&F4&G4&E5&F5&G5&E6&F6&G6&E7&F7&G7&E8&F8&G8&E9&F9&G9&E10&F10&G10&E11&F11&G11&E12&F12&G12))
  14. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s