Countif function throwing error with named range

Hi all,

Stumped on this one. I have a named range "metrics" which evaluates to the values {3367,1066,789,496,496,379,371}. (The named range definition is a formula using the LARGE function to sort the results of an OFFSET lookup.)

I want to count the number of times a particular value appears in the array of values, so I expect this to work:

=COUNTIF(metrics,496)

This returns a #VALUE error. Using Excel's Evaluate Formula tool, I see the named range expand out to the array of values just fine, then the COUNTIF function throws the error. Everything I've seen around the net indicates COUNTIF should work with a named range.

There's no problem with the named range and it refers to numbers (ex. =SUM(metrics) evaluates no problem). I'm happy to post a sample workbook if it'd be helpful.

Any idea what's going on here? I'd appreciate any insight!

Thanks,
Keith

Try this one.

=COUNTIF(metrics,"496")

Hi Oeldere,

Tried that, same result. I also tried putting my criteria in a cell, =COUNTIF(metrics,E2) and that failed.

The only thing I've found to work is dumping the named range back out into cell values (ex. A2:A8={metrics}) and running COUNTIF on the explicit range, =COUNTIF(A2:A8,496) which of course defeats the point of using a named range.

Thanks!

Is the named range contiguous?

Hi Cutter,

Sort of. The definition is =LARGE(unorderedMetrics,ROW(1:16))

The definition of the named range unorderedMetrics is =OFFSET(ref point,0,MATCH(date,range of dates,0),50,1)

So, unorderedMetrics returns an array of 50 unordered values (selection of contiguous cells in a column, based on matching a date in a header row of dates) and metrics returns the largest 16 of those values.

Clear as mud? :-) I've attached a workbook with the problem extracted and replicated.

Haseeb, shg,

Thanks for joining in! That seems to be the problem - COUNTIF expecting a range, not a named formula.

I'm still not necessarily clear on the difference between a range and an array (is it that an array has no address associated with each value?).

I don't suppose there's an easy work-around for converting the values of a named formula into a range?

shg - any way to do that without throwing the named formula results into cells on the page?

All - I'm going to mark this solved. It came out of my attempts to solve this problem http://www.excelforum.com/excel-gene...08#post2817608.

Thanks again for your help!
Keith

LOL Fair enough, and nicely phrased! :-)

Hi,
I was getting the same value error for ditto formulae, I had a formulae =COUNTIFS(_5.1_Owner,$A19,Result_5.1,D$18).

Here, for the second named range, it had lesser range than first. When 2nd named ranges was made to refer to exact same range, the problem disappeared!

Thanks,
Narendra