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?

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

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?

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

=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?