Conditional Max

B

blatham

If I have a 2 columns of data like this:

Col A Col B
a 1
b 2
c 8
a 5
b 6
c 1

What is the best way to retrieve the maximum entry in Col B for a
specific entry in Col A.
 
R

Ron Coderre

Try this array formula*:

For values in A1:B10

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

*Note: For array formulas, hold down [Ctrl] and [Shift] when you press
[Enter].

Does that help?

Regards,
Ron
 
G

Gary''s Student

=SUBTOTAL(104,B:B)

SUBTOTAL can return any of the following:

1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP

Where the first number includes hidden values and the second ignores them.

By using AutoFIlter on your columns, you can display only the a values and
the formula will only consider them.
 
Top