max value from multiple rows of similar information

[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. Valko]

Try this array formula** :

=MAX(IF(A1:A10="white",D110))

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

 

[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. Valko]

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

F1 = white

=MAX(IF(A1:A10=F1,D110))

Don't forget, array entered!

 

[Karesel]

Thanks so much it's working...

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

F1 = white

=MAX(IF(A1:A10=F1,D110))

Don't forget, array entered!

 

[T. Valko]

You're welcome. Thanks for the feedback!