## Friday, May 10, 2013

### yogi_Compute Total Number of Animals Of Different Colors Based On Specified Animals And Colors

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 11, 2013
Suppressing array-enabling within an ARRAYFORMULA expression?
Specifically, I have a situation where I'm using FILTER to search for a particular value V within a range header RH, that then returns a value in a range R, and I would like - ideally - to be able to iterate this behaviour over a range representing different values of V, while keeping the filtered ranges R and RH the same.

That is to say, I would like to boil down the following into one expression:

{ FILTER( R, RH=V1 ), FILTER( R, RH=V2 ), FILTER( R, RH=V3 )... }

Two points to note:
- I am attempting to keep this confined within one cell. I would eventually be using the results of the array in a SUM or the like.
- The actual thing I'm working on is a little more complicated than the above, but this is the core of the issue.

Obviously, attempting to do this with an ARRAYFORMULA expression results in errors as it tries to iterate through R and RH also. Hence the question - is there any way of suppressing this behaviour for particular ranges?

Alternatively, it is entirely possible I am thinking about this problem inside-out or backwards, and I would welcome any suggestions for how else I might achieve this - the only condition being that it remains within one cell.

Is what I want to do even possible?

I have considered writing a custom function that takes VX as an argument, and so 'hides' the array-enabling functionality, but this seems to have significant downsides - specifically that (as far as I understand it) the result would not automatically update if the data in R changes.

---
Well, I'm pretty sure I've given as concrete an explanation as I can, but I have made an example sheet to demonstrate the idea, including a little bit more of the relevant complications: