need function to sum top ranking items in list
 

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

QuantumPion wrote...
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?

SUMIF only provides one criterion. Use the array formula

=SUM(LARGE(IF(ISNUMBER(List),IF(List<X,List)),ROW( INDIRECT("1:32"))))

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"
wrote in message
...

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

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LARGE(IF(IF(ISNUMBER(rng),rng)<X,IF(ISNUMBER( rng),rng,0)),
ROW(INDIRECT("1:32"))))

In article ,
QuantumPion
wrote:

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?

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:3 2"))))

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

In article ,
"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

--
HTH

Bob Phillips

"QuantumPion"
wrote in message
...

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?

In article .com,
"Harlan Grove" wrote:

QuantumPion wrote...
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?

SUMIF only provides one criterion. Use the array formula

=SUM(LARGE(IF(ISNUMBER(List),IF(List<X,List)),ROW( INDIRECT("1:32"))))

Note that returns #N/A if the List contains #N/A...

Here is a more detailed explanation. I need a function that will sum the
n largest values in an array, where the values are less then x and are
not #N/A, meet a criteria in a seperate array, and where the total of
the sum is less then or equal to y. How can I do this? Thanks. :)

For example, here is my data:


Code:
--------------------

name type value

alpha g 1
bravo 5
charlie g 2
delta g #N/A
echo 3
foxtrot g 7

--------------------


I want a function that will find the sum of the largest two values that
are "g" that add to 8 or less. So the output would look like:


Code:
--------------------

name value
alpha 1
foxtrot 7

sum: 8

--------------------


--
QuantumPion
------------------------------------------------------------------------
QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991
View this thread: http://www.excelforum.com/showthread...hreadid=376101

now that I think about it, this is much more complicated. I have
multiple "value" columns for different dates. And I need to pick the
highest 32 items that meet the criteria for each date, but none of the
items can be used twice. So an additional criteria is that none of the
32 items could have been used in the previous column. Bleh! I'll
probably have to do this manually.


--
QuantumPion
------------------------------------------------------------------------
QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991
View this thread: http://www.excelforum.com/showthread...hreadid=376101

JE McGimpsey wrote...
"Harlan Grove" wrote:
....
SUMIF only provides one criterion. Use the array formula

=SUM(LARGE(IF(ISNUMBER(List),IF(List<X,List)),RO W(INDIRECT("1:32"))))

Note that returns #N/A if the List contains #N/A...

Did you test that assertion? Apparently not.

Note that I use *2* *nested* IF calls. The first in effect passes only
numbers on to the second. Any #N/A in List (or any other error values,
text or boolean values) will produce a FALSE value in the result array
from the outer IF call.

A simplified example, if A1:A6 contained

1
#N/A
3
4
5
6

and B1 contained the *ARRAY* formula

=SUM(LARGE(IF(ISNUMBER(A1:A6),IF(A1:A6<5,A1:A6)),R OW(INDIRECT("1:2"))))

the formula would return 7, not #N/A, because the formula would
evaluate as

=SUM(LARGE(IF(ISNUMBER({1;#N/A;3;4;5;6}),
IF({1;#N/A;3;4;5;6}<5,{1;#N/A;3;4;5;6})),{1;2}))

=SUM(LARGE(IF({TRUE;FALSE;TRUE;TRUE;TRUE;TRUE},
IF({TRUE;#N/A;TRUE;TRUE;FALSE;FALSE},{1;#N/A;3;4;5;6})),{1;2}))

=SUM(LARGE(IF({TRUE;FALSE;TRUE;TRUE;TRUE;TRUE},
{1;#N/A;3;4;FALSE;FALSE}),{1;2}))

=SUM(LARGE({1;FALSE;3;4;FALSE;FALSE},{1;2}))

=SUM({4;3})

=7

JE McGimpsey wrote...
One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=SUM(LARGE(IF(IF(ISNUMBER(rng),rng)<X,IF(ISNUMBER (rng),rng,0)),
ROW(INDIRECT("1:32"))))
....

IF X is a number, the 3rd IF call, IF(ISNUMBER(rng),rng,0), is
unnecessary and should be replaced with just rng. Note that this relies
on the obscure fact that FALSE evaluates greater than any number value.

True. Thanks for the correction!

In article .com,
"Harlan Grove" wrote:

IF X is a number, the 3rd IF call, IF(ISNUMBER(rng),rng,0), is
unnecessary and should be replaced with just rng. Note that this relies
on the obscure fact that FALSE evaluates greater than any number value.

Actually I did, but I can't replicate it - must have been the same
problem that caused me to add the extra if() in my formula.

Sorry!

In article .com,
"Harlan Grove" wrote:

Did you test that assertion? Apparently not.

My problem is trying to make a forumla that adds the highest values that
total less then a limit though. What I need is a way to repeat a forumla
until a result is achieved, sort of like:

if(sum(rank(1:32)1500,if(sum(rank(2:33)1500,if(s um(rank(3:34)1500,
... ad nausem.


--
QuantumPion
------------------------------------------------------------------------
QuantumPion's Profile: http://www.excelforum.com/member.php...o&userid=23991
View this thread: http://www.excelforum.com/showthread...hreadid=376101