MAX only Rows in Range where another Row is Not Blank
 

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

MAX only Rows in Range where another Row is Not Blank
 

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)

--
Biff
Microsoft Excel MVP


"PCLIVE" wrote in message
...
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
--

MAX only Rows in Range where another Row is Not Blank
 

That's it. Thanks.

--

"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"PCLIVE" wrote in message
...
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
--

MAX only Rows in Range where another Row is Not Blank
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"PCLIVE" wrote in message
...
That's it. Thanks.

--

"T. Valko" wrote in message
...
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)

--
Biff
Microsoft Excel MVP


"PCLIVE" wrote in message
...
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
--