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.

 

[Montetrebol]

Additional information:

If I define the range as usual, and the criteria as a reference to a single-cell named range (i.e. "year",=COUNTIF($A1:$A20,year)) it works just fine! The problem happens if the named range has multiple cells.

Thanks,

J.

 

[Aladin Akyurek]

Additional information:

If I define the range as usual, and the criteria as a reference to a single-cell named range (i.e. "year",=COUNTIF($A1:$A20,year)) it works just fine! The problem happens if the named range has multiple cells.

Thanks,

J.

Care to post the formula and the cells the named range consists of?

 

[Marcelo Branco]

Additional information:

If I define the range as usual, and the criteria as a reference to a single-cell named range (i.e. "year",=COUNTIF($A1:$A20,year)) it works just fine! The problem happens if the named range has multiple cells.

Thanks,

J.

Not sure if i understand what you need.

See if this helps

=SUMPRODUCT(COUNTIF($A1:$A20,year))

M.

 

[Montetrebol]

I defined a simple range NumsToCount on A1:A5 with values 1,2,3,2,1. Then I defined the named range Two as cell A2. =COUNTIF(A1:A5,A2) works, =COUNTIF(A1:A5,Two) works, but =COUNTIF(NumsToCount,A2) does not work.

I should clarify that my excel is in spanish so the formulas actually look like this: =CONTAR.SI(A1:A5|A2), =CONTAR.SI(A1:A5|Two), =CONTAR.SI(NumsToCount|A2).

 

[Aladin Akyurek]

I defined a simple range NumsToCount on A1:A5 with values 1,2,3,2,1. Then I defined the named range Two as cell A2. =COUNTIF(A1:A5,A2) works, =COUNTIF(A1:A5,Two) works, but =COUNTIF(NumsToCount,A2) does not work.

I should clarify that my excel is in spanish so the formulas actually look like this: =CONTAR.SI(A1:A5|A2), =CONTAR.SI(A1:A5|Two), =CONTAR.SI(NumsToCount|A2).

What is in A1 exactly?
And what is in A2?

 

[Montetrebol]

A1 is the number 1, A2=2, A3=3, A4=2, and A5=1.

 

[Montetrebol]

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!

I defined a simple range NumsToCount on A1:A5 with values 1,2,3,2,1. Then I defined the named range Two as cell A2. =COUNTIF(A1:A5,A2) works, =COUNTIF(A1:A5,Two) works, but =COUNTIF(NumsToCount,A2) does not work.

I should clarify that my excel is in spanish so the formulas actually look like this: =CONTAR.SI(A1:A5|A2), =CONTAR.SI(A1:A5|Two), =CONTAR.SI(NumsToCount|A2).

 

[Aladin Akyurek]

A1 is the number 1, A2=2, A3=3, A4=2, and A5=1.

=COUNTIF(A1:A5,A1)

==> 2

If you name A2 as Two (using the Name Manager or the Name Box)...

=COUNTIF(A1:A5,A2)

==> 2

If you name A1:A5 as NumsToCount...

=COUNTIF(NumsToCount,A1)

==> 2

=COUNTIF(NumsToCount,Two)

==> 2

 

[Montetrebol]

To Aladin Akyurek :

Yes, the problem was my list separator, I had changed it from "," to "|" and it messed things up.

Thanks a lot.