On Feb 15, 10:57 pm, "Sandy Mann" wrote:
Something is not working, but I don't know why, since it works just
fine with LARGE.

Ron has already given you an explanation. One way around it is to use an
IF() statement with a very large numbers as its FALSE element:

=SUM(SMALL(IF(B1:B6=1,A1:A6,1E+307),ROW(INDIRECT(" 1:2"))))

Still array entered.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk

"hdf" wrote in message

...

On Feb 15, 10:07 pm, Ron Rosenfeld wrote:
On Fri, 15 Feb 2008 12:27:13 -0800 (PST), hdf wrote:
Ron,

Thank you, I will try your suggestion. However, what I don't
understand is that my range TABLE has no 0's in it, so they should not
be counted, otherwise everytime you use the SMALL function it would
give you a value of 0. The smallest number in my TABLE is 1.

Am I missing something here?

Yes you are. You are not understanding what your formula is doing.

Your SMALL function is evaluating the results of the array generated by
this
formula:

(TARGET=1)*TABLE

TARGET=1 evaluates to {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE}

TABLE evaluates to {1;2;3;4;5;6}

When you multiply one by the other, you get {1;2;3;0;0;0}

The two smallest numbers in that array are both 0.

--ron

I thought by doing an array with SUM as in this case, the * was
interpreted as an AND function. It should not be multiplying them -
it should be summing the value of the events that meet the criteria -
as it does in my LARGE example where the answer is 5 (2 + 3). If it
was multiplying it would be an answer of 6.

When I use the same formula with with more than one range in
Parantheses and separated by a * (e.g. a second condition), it works
as expected with LARGE (summing the two largest values of the
resultant data set that has been filtered by my conditions- it does
not multiply them).

Something is not working, but I don't know why, since it works just
fine with LARGE.

Thanks,

Hector

Thanks all of you for your help.