Select items based on criteria and return in an array

I'd like to select items in a way like Sumifs or Countifs does and then return all of those to a function.

So, assuming SomeIFS acts like Countifs (but returns the values and doesn't do a count) I'd end up with: =Somefunction(SomeIFS(A1:B10,">10"))

Is this possible?

Thanks,
Al

To walk through this....this works.

{=SUM((Table1[Crit1]=1)*(Table1[Crit2]="B")*(Table1[Value]))}

{=((Table1[Crit1]=1)*(Table1[Crit2]="B")*(Table1[Value]))}

on it's own returns an array that looks something like:
{2;1;0;0;0;4;7;2}

and then you sum it and works just fine.

but this doesn't

{=boxplot((Table1[Crit1]=1)*(Table1[Crit2]="B")*(Table1[Value]))}

it should become:
=boxplot(2;1;0;0;0;4;7;2)
which should work great....
but it comes back as #VALUE.

So, I'm perplexed as to why the same within a sparkline formula doesn't work.

Any ideas?

Is there any way to force the array into a range?

hi martin,

to clarify what I'm trying to achieve...

well, I'm using an add-in that can't handle #VALUE / #NA values in its input range.

normally, for it's input range you would just say {1;2;3;4;5} or A1:A10 or Table1[Column1]

But, i want multiply a number of columns in a table together as a way of selecting a criteria and returning only a subset of the values in one of the colums. (sort of the SUMPRODUCT trick but instead of returning a SUM want to return a range of values)

This method works with the built-in Excel functions like SUM or Average (or any of them really) because the built-in functions understand the #VALUE/#N?A values and just strip them out.

This add-in can't do that.

Does that clarify why i want to take an Array with values + #N/A or #Values and return just the number values?

(the add-in by the way is the very cool: sparklines-excel.blogspot.com )

Thanks,
Al