HSTACK VSTACK and COUNTIF

[TomCon]

I have just recently discovered HSTACK and VSTACK.

Are they essentially a substitution for wherever a function has a "range" argument?

It seems to be "MOSTLY" so!

For instance, this is working here for SUM, AVERAGE
=AVERAGE(VSTACK([@SiEv2B],[@SiEv3],[@SiEv13],[@SiEv14],[@SiEv15],[@SiEv16],[@SiEv18B],[@[SiEv19B.b]],[@SiEv20b],[@[SiEv21.b]],[@SiEv22],[@SiEv23]))
=SUM(VSTACK([@SiEv2B],[@SiEv3],[@SiEv13],[@SiEv14],[@SiEv15],[@SiEv16],[@SiEv18B],[@[SiEv19B.b]],[@SiEv20b],[@[SiEv21.b]],[@SiEv22],[@SiEv23]))

It does not work for COUNTIF. For COUNTIF, Excel will not even let me enter the formula, it just gives you the generic error message "Are you trying to type a formula, start with a = sign..."
=COUNTIF(VSTACK([@SiEv2B],[@SiEv3],[@SiEv13],[@SiEv14],[@SiEv15],[@SiEv16],[@SiEv18B],[@[SiEv19B.b]],[@SiEv20b],[@[SiEv21.b]],[@SiEv22],[@SiEv23]),0)

Thanks if you can clarify this for me, or an idea of how to use COUNTIF with a non-contiguous range.

 

[jasonb75]

They are not substitutes for range arguments, they consolidate multiple ranges or arrays into a single row or column. The output is an array not a range. As such it will not work with any function where a range is required, only those that will accept arrays.

Try

=SUMPRODUCT(--(VSTACK([@SiEv2B],[@SiEv3],[@SiEv13],[@SiEv14],[@SiEv15],[@SiEv16],[@SiEv18B],[@[SiEv19B.b]],[@SiEv20b],[@[SiEv21.b]],[@SiEv22],[@SiEv23])=0))

 

[TomCon]

They are not substitutes for range arguments, they consolidate multiple ranges or arrays into a single row or column. The output is an array not a range. As such it will not work with any function where a range is required, only those that will accept arrays.

Try

=SUMPRODUCT(--(VSTACK([@SiEv2B],[@SiEv3],[@SiEv13],[@SiEv14],[@SiEv15],[@SiEv16],[@SiEv18B],[@[SiEv19B.b]],[@SiEv20b],[@[SiEv21.b]],[@SiEv22],[@SiEv23])=0))

Thanks, that works. I have never really fully understood the difference between Excel functions that allow "array" vs. "range". Is there a way to tell in Excel which is which, or just by experimenting?

 

[jasonb75]

Is there a way to tell in Excel which is which

When you look at the tooltips as you type the formula those that only accept ranges will either say Range, or Ref / Reference.

There may be some exceptions, one that comes to mind is AGGREGATE, which has 2 sets of tooltips and will accept arrays for some function numbers, but not all. See posts 5 and 7 here for more on that Use of AGGREGATE

 

[TomCon]

Thanks! I'll try to start noticiing the difference. It may matter more now since we have HSTACK and VSTACK.

 

[jasonb75]

If you were not aware, there are a few functions that do return a range and as such can be nested into functions that do require a range. That said, trying to pass multiple ranges to a single argument as would be required for your original attempt would still not be easily done (if at all possible, I haven't attempted it).

INDEX, OFFSET, and INDIRECT are the main ones that come to mind, there may be a few others. (INDEX depends on data source and other steps).
Things like IF and CHOOSE can pass ranges as long as they are not manipulated by a previous step in the formula, which could likely convert the data to an array.