need function to sum top ranking items in list

[QuantumPion]

How can I make a function that will sum the largest 32 items in a colum
which meet specific criteria? For example, the largest 32 values tha
are smaller then X, or are not #N/A? I think I want to use SUMIF, bu
how can I format the criteria to evaluate a function for each cell

 

[Harlan Grove]

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"))))

 

[Bob Phillips]

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]...

 

[JE McGimpsey]

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"))))

 

[Harlan Grove]

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"))))

 

[JE McGimpsey]

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

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

 

[JE McGimpsey]

QuantumPion wrote...

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...

 

[QuantumPion]

Here is a more detailed explanation. I need a function that will sum th
n largest values in an array, where the values are less then x and ar
not #N/A, meet a criteria in a seperate array, and where the total o
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 tha
are "g" that add to 8 or less. So the output would look like:


Code
-------------------

name value
alpha 1
foxtrot 7

sum: 8

 

[QuantumPion]

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

 

[Harlan Grove]

JE McGimpsey wrote...

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)),ROW(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

 

[Harlan Grove]

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.

 

[JE McGimpsey]

True. Thanks for the correction!

 

[JE McGimpsey]

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!

 

[QuantumPion]

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(sum(rank(3:34)>1500,
.... ad nausem.