Subtotal minimum number that isn't zero

Diesel9a1

New Member
Joined
Feb 12, 2015
Messages
37
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?

Thanks in advance.
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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:
Upvote 0
Some other options:

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
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
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top