Named range(s) of non-adjacent cells return #VALUE! error in array formulas

[cgreen07]

I have ranges made up of non-adjacent cells from one worksheet and I
get a #VALUE! error when I try to do any conditional count or sum
calculation with an array formula on either or both of them. Each
range is a selection of 32 non-adjacent cells from a single column.
The cells contain array formulas that return percentages.

Example of range values
I8: 5.9%
I11: 12.1%
I14: 22.3%
I17: 0.0%

I was able to get values returned from simple functions like Max and
Min but the following example gives me the #VALUE! error:

{=SUM(IF((Rng>0),1,0))}
=COUNTIF(Rng,"<.0125")

Any wisdom would be appreciated!! Thanks

 

[hrlngrv]

(e-mail address removed) wrote...

I have ranges made up of non-adjacent cells from one worksheet and I
get a #VALUE! error when I try to do any conditional count or sum
calculation with an array formula on either or both of them. Each
range is a selection of 32 non-adjacent cells from a single column.
The cells contain array formulas that return percentages.

Example of range values
I8: 5.9%
I11: 12.1%
I14: 22.3%
I17: 0.0%

I was able to get values returned from simple functions like Max and
Min but the following example gives me the #VALUE! error:

{=SUM(IF((Rng>0),1,0))}
=COUNTIF(Rng,"<.0125")

Rng is a multiple area range, and such ranges can't be converted to
arrays because there's no generally consistent way to do so. That is,
Excel can't differentiate between (A1,A3,A5) and (A1,A2:B5,A3). Both
are 3 area ranges. While you may believe the first should decompose
into an array of the values of A1, A3 and A5, Excel must threat it no
differently than the second one, which clearly can't decompose into a
simple array.

So you can't use multiple area ranges in array formulas. Furthermore,
in it's infinite wisdom (patience with my sarcasm), Microsoft chose to
implement SUMIF and COUNTIF so that their first arguments (and SUMIF's
third argument) must be single area ranges. There's no obvious good
reason why they did this, but once done it can't be changed.

Workaround for conditional counting,

=INDEX(FREQUENCY(Rng,TargetValue-{1E-12,0}),1)

returns the count of numbers in Rng < TargetValue,

=INDEX(FREQUENCY(Rng,TargetValue-{1E-12,0}),2)

returns the count of numbers in Rng = TargetValue, and

=INDEX(FREQUENCY(Rng,TargetValue-{1E-12,0}),3)

returns the count of numbers in Rng > TargetValue. Going further,

=INDEX(FREQUENCY(Rng,TargetValue+{1E-12}),2)

returns the count of numbers in Rng <= TargetValue,

=INDEX(FREQUENCY(Rng,TargetValue-{1E-12}),1)

returns the count of numbers in Rng >= TargetValue, and

=COUNT(Rng)-=INDEX(FREQUENCY(Rng,TargetValue-{1E-12,0}),2)

returns the count of numbers in Rng <> TargetValue. Unfortunately,
there's no analagous workaround for conditional sums. However, your
example range above included every third cell between I8 and I17. If
your multiple area ranges always conform to such regular patterns, you
could use OFFSET.
=SUMPRODUCT(--(N(OFFSET($I$8,3*(ROW(INDIRECT("1:4"))-1),0))<0.0125))

 

[cgreen07]

Thanks for your prompt reply! You are right about the pattern of every
3rd cell. I have been trying out the formula:

=SUMPRODUCT(--(N(OFFSET($I$8,3*(ROW(INDIRECT("1:4"))-1),0))<0.0125))

and I seem to be getting a count of values that meet the criteria. Did
I misunderstand you in thinking that I would get a sum of those values
that meet the criteria? I will continue to work it and see if I can
manipulate a sum from it.

 

[Harlan Grove]

Thanks for your prompt reply! You are right about the pattern of every
3rd cell. I have been trying out the formula:

=SUMPRODUCT(--(N(OFFSET($I$8,3*(ROW(INDIRECT("1:4"))-1),0))<0.0125))

and I seem to be getting a count of values that meet the criteria. Did
I misunderstand you in thinking that I would get a sum of those values
that meet the criteria? I will continue to work it and see if I can
manipulate a sum from it.

Yup, I screwed up and forgot to repeat the argument.

=SUMPRODUCT(--(N(OFFSET($I$8,3*(ROW(INDIRECT("1:4"))-1),0))<0.0125),
N(OFFSET($I$8,3*(ROW(INDIRECT("1:4"))-1),0)))

 

[cgreen07]

Fantastic! That formula is exactly what I needed! I have learned
quite a bit in trying to solve this problem but I couldn't have done it
without your help, thanks!