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

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.

Solution next week. Best of luck!

1. John Jairo V says:

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. GreasySpot says:

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. GreasySpot says:

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. John Jairo V says:

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. GreasySpot says:

Sweet, I didn’t know it worked that way.

6. Bill Szysz says:
`=SUM(--(MMULT(--(COUNTIFS(A3:C12,E3:G12)>0),{1;1;1})=3))`

No need CSE

7. Bill Szysz says:

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

`=SUM(--(MMULT(--(COUNTIFS(A3:C12,E3:G12)>0),{1,1,1})=3))`
8. Bill Szysz says:

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

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

10. Bill Szysz says:

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))`
11. That one does require CSE.

Regards

12. James says:

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))`
13. ChrisBM says:

Certainly the shortest I can come up with is:

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

15. ChrisBM says:

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.

16. ChrisBM says:

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))`

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.

Also, could someone explain to me what CSE is?

18. James says:

@ Isai

CSE = Control+Shift+Enter …