If possible, sort your data so that the dynamic range will be contiguous.
That's the easiest way.

"rolando" wrote:

yes, you are right, discontineous range
now it does work
except the countif
need to define multi-range and split the formula in parts

"~L" wrote:

For me, Average worked on discontiguous ranges, but produced #N/A when the
#N/A error was included in the range.

Countif doesn't work on discontiguous ranges, giving you the #VALUE! error.
This seems to be the case even when using a named range.

So, is it true that your range contained #N/A and was discontiguous?

"rolando" wrote:

#N/A when I use average, #VALUE when I use countif

"~L" wrote:

What error do you receive when you use that function on that range?

"rolando" wrote:

why can't I use functions on a defined multiple range ?
eaxample; average(rangetotal) where rangetotal is defined as cells A1-A5 +
A7-A10
(Rangetotal is a dynamical range which grows in size and number of multi
ranges)