• Status: Solved
• Priority: Medium
• Security: Public
• Views: 345

# excel formula

Please can you help with the formula in sheet2 D11

I want to return the Max number within the 'filtered' dates (D5:D6)

So the result should equal 12

help.xls
0
Gazza83
• 3
1 Solution

ConsultantCommented:
I can't tell exactly what you are trying to do from the example but is you want to only look at filtered values then use the SUMPRODUCT function as described below.

The SUBTOTAL function provides various aggregate functions such as SUM and AVERAGE that ignore hidden rows in AutoFilter lists. The following parameter values only ignore rows hidden with the AutoFilter function and are compatible with all versions of Excel.

1 - AVERAGE
2 - COUNT
3 - COUNTA
4 - MAX
5 - MIN
6 - PRODUCT
7 - STDEV
8 - STDEVP
9 - SUM
10 - VAR
11 - VARP

In Excel 2003 and later one of the features of the SUBTOTAL function is that it can, if specified, ignore any rows in the input range that are manually hidden. To use this feature specify one of the parameter values listed below for the first parameter. Note that SUBTOTAL ignores rows hidden using AutoFilter regardless of what the parameter is.

101 - AVERAGE
102 - COUNT
103 - COUNTA
104 - MAX
105 - MIN
106 - PRODUCT
107 - STDEV
108 - STDEVP
109 - SUM
110 - VAR
111 - VARP

Kevin
0

Microsoft MVP ExcelCommented:
Hello,

the attached list is not filtered. To return the maximum number you can use

=max(D5:D6)

cheers, teylyn
0

Author Commented:
I only want to return data that falls between the criteria listed in both D5:D6 "Stats table"

So the =SUMPRODUCT((Data!B14:B20000+0>=D5)*(Data!B14:B20000+0<=D6)* part is trying to match up the dates from D5:D6 with those in coumn B "Data" sheet

Unfortunatly if I use "=MAX(Data!X14:X2000)" this would return data that doesn't always fall between the dates in D5:D6

i.e B19 = 11/03/2010

0

ConsultantCommented:
Use this array formula:

=MAX(IF((Data!B14:B20000>=D5)*(Data!B14:B20000<=D6)>0,Data!B14:B20000,""))

Enter by pressing CTRL+SHIFT+ENTER.

Kevin
0

ConsultantCommented:
Correction:

=MAX(IF((Data!B14:B20000>=D5)*(Data!B14:B20000<=D6)>0,Data!X14:X20000,""))

Kevin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.