MAX only Rows in Range where another Row is Not Blank

[PCLIVE]

I have some staggard data. There are categories in column D. There are
sub-categories in column E. Then some Descriptions in column F. And
finally there are some corresponding numbers in column H. I'm trying to do
a MAX function only on the values in column H where the cell in the same row
in column F is NOT blank. So in my example, the max function should only be
looking at rows (3,4,6,9,10,12,15,17,19,21,23). The MAX of the numbers in
column H of these rows, as you know, would be 21. Is there a way to do
this? Thanks. Paul

D E F G H
1 Cat 24
2 SubCat 22
3 Desc1 20
4 Desc2 2
5 SubCat2 22
6 Desc1 2
7 Cat2 22
8 SubCat1 14
9 Desc1 8
10 Desc2 6
11 SubCat2 8
12 Desc1 8
13 Cat3 21
14 SubCat1 10
15 Desc1 21
16 SubCat2 6
17 Desc1 6
18 SubCat3 2
19 Desc1 2
20 SubCat4 2
21 Desc1 2
22 SubCat5 1
23 Desc1 1
--

 

[T. Valko]

Try this array formula** :

=MAX(IF(F1:F23<>"",H1:H23))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[PCLIVE]

That's it. Thanks.

--

Try this array formula** :

=MAX(IF(F1:F23<>"",H1:H23))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

That's it. Thanks.

--