# Subtotal minimum number that isn't zero

#### Diesel9a1

##### New Member
Trying to get the following, but cannot figure the formula to get my desired output described.

Subtotal(5,H4:H15) - the minimum number in a filtered list that isn't zero.

Likewise,

Subtotal(4,H4:H15) - the max number in a filtered list that is below 400 (or whatever I decide)

Any thoughts on this one please?

#### tyija1995

##### Well-known Member
Hey,

For your first formula (the min in a filtered list that isn't zero) use this formula:

=IF(AGGREGATE(5,7,H4:H15)=0,MINIFS(H4:H15,H4:H15,">0"),AGGREGATE(5,7,H4:H15))

For the second formula (the max in a filtered list that is no greater than 400) use this formula:

=IF(AGGREGATE(4,7,H4:H15)>400,MAXIFS(H4:H15,H4:H15,"<=400"),AGGREGATE(4,7,H4:H15))

Last edited:

#### Eric W

##### MrExcel MVP
Some other options:

ABCDE
1
2Min over 0Max under 400
4118
52
60
88
9500
10

</tbody>
Sheet1

Worksheet Formulas
CellFormula
D4=AGGREGATE(15,6,1/(1/SUBTOTAL(109,OFFSET(B1,ROW(B1:B10)-ROW(B1),0))),1)
E4=AGGREGATE(14,6,SUBTOTAL(109,OFFSET(B1,ROW(B1:B10)-ROW(B1),0))/(SUBTOTAL(109,OFFSET(B1,ROW(B1:B10)-ROW(B1),0))<400),1)

</tbody>

<tbody>
</tbody>

#### DanteAmor

##### Well-known Member
Options with array formula:

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:115.96px;" /><col style="width:135.92px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >H</td><td >I</td><td >J</td><td >K</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td style="background-color:#92d050; ">min (no zero)</td><td style="background-color:#92d050; ">max below 400</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">HEAD1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">HEAD8</td><td > </td><td style="text-align:right; ">2</td><td style="text-align:right; ">10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >A</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >A</td><td style="text-align:right; ">10</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >A</td><td style="text-align:right; ">4</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td >A</td><td style="text-align:right; ">2</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td >A</td><td style="text-align:right; ">500</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td >A</td><td style="text-align:right; ">5</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >J3</td><td >{=MIN(IF(SUBTOTAL(9,OFFSET(\$H\$4,ROW(\$H\$4:\$H\$21)-ROW(H4),,1))*(\$H\$4:\$H\$21>0),\$H\$4:\$H\$21))}</td></tr><tr><td >K3</td><td >{=MAX(SUBTOTAL(9,OFFSET(\$H\$4,ROW(\$H\$4:\$H\$21)-ROW(H4),,1))*(\$H\$4:\$H\$21<400))}</td></tr></table></td></tr></table>

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

#### Diesel9a1

##### New Member
Some great options there folks. All work well. Thank you for your help on this one.

1,081,676
Messages
5,360,441
Members
400,586
Latest member
Minty

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...