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

                                          Google Spreadsheet   Post  #1188
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   May 11, 2013
user louster (http://productforums.google.com/forum/?zx=gt4fhhtub7z3#!category-topic/docs/spreadsheets/65CEMEANMoA)
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:

https://docs.google.com/spreadsheet/ccc?key=0AgH_yBhzPz1tdGdsX096ekNPbXRtV3l3TGlxSGpWWmc&usp=sharing

It would be relatively trivial to make a third column containing intermediate calculations that could then be summed, but it would be far better for my purposes if it was possible to perform the entire calculation within one cell.

I also am loath to simply write out the FILTER expressions N times within one cell. That would be very bad.

Does the sheet make my query any clearer? Could the FILTER expression be rewritten in any way that would allow it to be used with ARRAYFORMULA or the like?
-------------------------------------------------------------------------------------------------------------------------
Well louster:  I am not sure I have understood your intent ... in any event please have a look at the following