FInding the largest number in a range meeting a criteria
 

Hmm... I though I'd understood how SUMPRODUCT works but clearly I haven't.
Basically what I want to do is just find the nth largest number in a range
where an adjacent range ="X". So if I have the following two columns (hope
the formatting sort of works):

A B
X 1
3
2
X 2
5
X 4

I thought I could somehow do this with
=LARGE(SUMPRODUCT(--(range1="X"),--(range2),n) But that just gives me the sum
of the values in range2 for which the value in range1 is "X" so obviously
SUMPRODUCT only returns a single number... I obviously don't get very far by
sticking LARGE inside the SUMPRODUCT, either... any ideas?

FInding the largest number in a range meeting a criteria
 

Naturally the formatting didn't work, but I'll try again. Anyway, with the
setup below I'd hope to get the formula to spit out 4:
A B
X 1
3
2
X 2
5
X 4

FInding the largest number in a range meeting a criteria
 

Try one of these array formula** :

=MAX(IF(A1:A10="x",B1:B10))

If you want the nth largest:

=LARGE(IF(A1:A10="x",B1:B10),n)

Where n = nth value

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Babymech" wrote in message
...
Hmm... I though I'd understood how SUMPRODUCT works but clearly I haven't.
Basically what I want to do is just find the nth largest number in a range
where an adjacent range ="X". So if I have the following two columns (hope
the formatting sort of works):

A B
X 1
3
2
X 2
5
X 4

I thought I could somehow do this with
=LARGE(SUMPRODUCT(--(range1="X"),--(range2),n) But that just gives me the
sum
of the values in range2 for which the value in range1 is "X" so obviously
SUMPRODUCT only returns a single number... I obviously don't get very far
by
sticking LARGE inside the SUMPRODUCT, either... any ideas?

FInding the largest number in a range meeting a criteria
 

Just need to find the LARGE before summing (as the sum of 1 number is that
number!)
=SUMPRODUCT(LARGE((Range1="x")*(Range2),n))
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Babymech" wrote:

Naturally the formatting didn't work, but I'll try again. Anyway, with the
setup below I'd hope to get the formula to spit out 4:
A B
X 1
3
2
X 2
5
X 4

FInding the largest number in a range meeting a criteria
 

=MAX(IF(A1:A7="X",B1:B7))

insert and hit ctrl+shift+enter

"Babymech" skrev:

Hmm... I though I'd understood how SUMPRODUCT works but clearly I haven't.
Basically what I want to do is just find the nth largest number in a range
where an adjacent range ="X". So if I have the following two columns (hope
the formatting sort of works):

A B
X 1
3
2
X 2
5
X 4

I thought I could somehow do this with
=LARGE(SUMPRODUCT(--(range1="X"),--(range2),n) But that just gives me the sum
of the values in range2 for which the value in range1 is "X" so obviously
SUMPRODUCT only returns a single number... I obviously don't get very far by
sticking LARGE inside the SUMPRODUCT, either... any ideas?