Problems with a contiguous, non-dynamic named range and COUNTIF

[Montetrebol]

Hi!

I was trying to use COUNTIF in a named range as it's explained here: Excel Magic Trick 185 Dynamic Formula Extract Data 1criteria - YouTube, but I receive a non descriptive error message as mentioned here: Ctrl + Shift + Enter: Excel Array Formulas 12: SUMIFS, COUNTIFS, SUMIF, COUNTIF, AVERAGEIF - YouTube. I even dowloaded the file used in the first video (http://people.highline.edu/mgirvin/YouTubeExcelIsFun/EMT185-202Finished.xls) and the formula works, but if I click on the cell with the COUNTIF, without even changing it, I get the error message.SUM() works just fine with the named range.

Based on these videos I seems to me as if Excel is forcing the "evaluation" of the named range and passing it to COUNTIF as an array

¿Could it be some obscure configuration setting that got changed somehow?

¿Could it be some plugin? I have 4 listed as active: ExcelToDBF, MySQL for Excel, NatSpeak Excel AddIn and Send to Bluetooth, but I don't know how to disactivate them in order to test if they are messing things up.

Any help would be greatly appreciated. Thanks a lot,

J.

 

[Aladin Akyurek]

FOUND THE PROBLEM!: I had changed the list separator from "," to "|" in the regional format configuration. I changed it back to "," and it works now, for some reason COUNTIF only likes "," as a list separator.

Thanks for your help!

On American (US) systems, the comma is the list seperator; On European systems, semi-colon (Dutch, English UK, etc.).