Finding the Max number in a filtered list.

[Graham C1600]

Hi,

Can somebody confirm the easiest way to find the maximum number in an excel column. The data in the column is filtered and there are also some Vlookup returns in the column so the columns will look something like this :-

10
8
2
#N/A
5
3
#N/A
3
#N/A
#N/A
1
2
#N/A
31
#N/A
2 The length of the column is about 500 rows. Thanks for any support on this issue.

<tbody>
</tbody><colgroup><col></colgroup>

 

[VBA Geek]

are you on XL2010 or later? then try

Excel 2010
	A	B	C
1	numbers		31
2	10
3	8
4	2
5	#N/A
6	5
7	3
8	#N/A
9	3
10	#N/A
11	#N/A
12	1
13	2
14	#N/A
15	31
16	#N/A
17	2
Sheet30

Cell Formulas
Range	Formula
C1	=AGGREGATE(14,7,A2:A17,1)

 

[AlanY]

you can try the subtotal() function that ignore hidden cells

=SUBTOTAL(104,A:A)

 

[Graham C1600]

Thanks for this it worked a treat. However is it possible to do this count based on certain criteria on other columns ? Say i had data in column D but not in all the cells, let's say cells D6 through to D13 had the word Yes in there. Can i get the max number in column A if column D=Yes ?

Thanks

 

[Graham C1600]

Thanks for this it worked a treat. However is it possible to do this count based on certain criteria on other columns ? Say i had data in column D but not in all the cells, let's say cells D6 through to D13 had the word Yes in there. Can i get the max number in column A if column D=Yes ?

Thanks

 

[VBA Geek]

Thanks for this it worked a treat. However is it possible to do this count based on certain criteria on other columns ? Say i had data in column D but not in all the cells, let's say cells D6 through to D13 had the word Yes in there. Can i get the max number in column A if column D=Yes ?

Thanks

Excel 2010
	A	B	C	D	E	F
1	numbers			condition		10
2	10			yes
3	8
4	2
5	#N/A
6	5			yes
7	3			yes
8	#N/A			yes
9	3			yes
10	#N/A			yes
11	#N/A			yes
12	1			yes
13	2			yes
14	#N/A
15	31
16	#N/A
17	2
Sheet6

Cell Formulas
Range	Formula
F1	{=MAX(IFERROR(SUBTOTAL(109,OFFSET(A2,ROW(A2:A17)-ROW(A2),0)),0)*(D2:D17="yes"))}
Press CTRL+SHIFT+ENTER to enter array formulas.