need function to sum top ranking items in list

Here is an example that sums the top 32 that are not equal to 35

=SUMPRODUCT(--(LARGE(IF(A1:A40<>35,A1:A40),ROW(INDIRECT("1:32")))))

it is an array formula, so commit with Ctrl-Shift-Enter

--
HTH

Bob Phillips

"QuantumPion" <[email protected]>
wrote in message
news:[email protected]...
>
> How can I make a function that will sum the largest 32 items in a column
> which meet specific criteria? For example, the largest 32 values that
> are smaller then X, or are not #N/A? I think I want to use SUMIF, but
> how can I format the criteria to evaluate a function for each cell?
>
>
> --
> QuantumPion
> ------------------------------------------------------------------------
> QuantumPion's Profile:
http://www.excelforum.com/member.php...o&userid=23991
> View this thread: http://www.excelforum.com/showthread...hreadid=376101
>

Bob Phillips wrote...
>Here is an example that sums the top 32 that are not equal to 35
>
>=SUMPRODUCT(--(LARGE(IF(A1:A40<>35,A1:A40),ROW(INDIRECT("1:32")))))
>
>it is an array formula, so commit with Ctrl-Shift-Enter
....

If it has to be an array formula, no point using SUMPRODUCT rather than
SUM. Also, LARGE only returns numbers, so no need for the '--' or the
parentheses enclosing the LARGE call.

=SUM(LARGE(IF(A1:A40<>35,A1:A40),ROW(INDIRECT("1:32"))))

That fails under the OP's "not #N/A" criterion.

In article <[email protected]>,
"Bob Phillips" <[email protected]> wrote:

> Here is an example that sums the top 32 that are not equal to 35
>
> =SUMPRODUCT(--(LARGE(IF(A1:A40<>35,A1:A40),ROW(INDIRECT("1:32")))))
>
> it is an array formula, so commit with Ctrl-Shift-Enter
>
> --
> HTH
>
> Bob Phillips
>
> "QuantumPion" <[email protected]>
> wrote in message
> news:[email protected]...
> >
> > How can I make a function that will sum the largest 32 items in a column
> > which meet specific criteria? For example, the largest 32 values that
> > are smaller then X, or are not #N/A? I think I want to use SUMIF, but
> > how can I format the criteria to evaluate a function for each cell?