max value from multiple rows of similar information

K

Karesel

- A B C D
1 BROOK PINE 5/25 47
2 BROOK PINE 5/26 21
3 BROOK PINE 5/27 33
4 BROOK PINE 5/28 49
5 BROOK PINE 5/29 19
6 WHITE SNOW 5/25 23
7 WHITE SNOW 5/26 21
8 WHITE SNOW 5/27 77
9 WHITE SNOW 5/28 46
10 WHITE SNOW 5/29 22

From the above I want obtain the maximum value from column D but only from
rows that match “WHITE†in column A which should be the 77 from cell D8.

Thanks in advance
 
T

T. Valko

Try this array formula** :

=MAX(IF(A1:A10="white",D1:D10))

** 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.
 
K

Karesel

Is there a way to get the formula to pick up the cell with "WHITE" in it,
rather than entering "white" in the formula itself?

Again, thanks in advance...
 
T

T. Valko

Sure, just replace "white" with the cell reference:

F1 = white

=MAX(IF(A1:A10=F1,D1:D10))

Don't forget, array entered!
 
K

Karesel

Thanks so much it's working...

T. Valko said:
Sure, just replace "white" with the cell reference:

F1 = white

=MAX(IF(A1:A10=F1,D1:D10))

Don't forget, array entered!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top