FInding the largest number in a range meeting a criteria

  • Thread starter Thread starter Babymech
  • Start date Start date
B

Babymech

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:
 
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.
 
Just need to find the LARGE before summing (as the sum of 1 number is that
number!)
=SUMPRODUCT(LARGE((Range1="x")*(Range2),n))
 
Back
Top