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 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" <[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
>
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 <[email protected]>,
QuantumPion <[email protected]>
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:32"))))
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:
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:
Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by QuantumPion; 06-02-2005 at 05:40 PM.
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.
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?
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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...
JE McGimpsey wrote...
>"Harlan Grove" <[email protected]> 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...
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
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 <[email protected]>,
"Harlan Grove" <[email protected]> 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 <[email protected]>,
"Harlan Grove" <[email protected]> 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(sum(rank(3:34)>1500, ... ad nausem.
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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...
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 <[email protected]>,
"Harlan Grove" <[email protected]> wrote:
> Did you test that assertion? Apparently not.
>
True. Thanks for the correction!
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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.
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 wrote...
>"Harlan Grove" <[email protected]> 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...
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
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?
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"))))
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 <[email protected]>,
QuantumPion <[email protected]>
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?
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
>
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"))))
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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...
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 <[email protected]>,
"Harlan Grove" <[email protected]> wrote:
> Did you test that assertion? Apparently not.
>
True. Thanks for the correction!
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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.
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 wrote...
>"Harlan Grove" <[email protected]> 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...
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
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?
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"))))
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 <[email protected]>,
QuantumPion <[email protected]>
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?
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
>
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 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"))))
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 <[email protected]>,
"Harlan Grove" <[email protected]> wrote:
> Did you test that assertion? Apparently not.
>
True. Thanks for the correction!
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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.
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 wrote...
>"Harlan Grove" <[email protected]> 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...
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
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?
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 <[email protected]>,
QuantumPion <[email protected]>
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?
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
>
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"))))
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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...
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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...
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 <[email protected]>,
"Harlan Grove" <[email protected]> wrote:
> Did you test that assertion? Apparently not.
>
True. Thanks for the correction!
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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.
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 wrote...
>"Harlan Grove" <[email protected]> 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...
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
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?
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"))))
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 <[email protected]>,
QuantumPion <[email protected]>
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?
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
>
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"))))
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 <[email protected]>,
QuantumPion <[email protected]>
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?
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 <[email protected]>,
"Harlan Grove" <[email protected]> wrote:
> Did you test that assertion? Apparently not.
>
True. Thanks for the correction!
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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.
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 wrote...
>"Harlan Grove" <[email protected]> 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...
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
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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...
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?
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"))))
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
>
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"))))
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" <[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
>
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 <[email protected]>,
QuantumPion <[email protected]>
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: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?
In article <[email protected]>,
"Harlan Grove" <[email protected]> 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...
JE McGimpsey wrote...
>"Harlan Grove" <[email protected]> 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...
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
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 <[email protected]>,
"Harlan Grove" <[email protected]> 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 <[email protected]>,
"Harlan Grove" <[email protected]> wrote:
> Did you test that assertion? Apparently not.
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks