Subtotal / Median IF

[vedro440]

Need help nestling subtotal into a median if formula. Greatly appreciate any help.

=IFERROR(MEDIAN(IF($H$12:$H$232=$C238,IF(ISNUMBER(L$12:L$232),IF(L$12:L$232>0,L$12:L$232)))),"")

[CAntosh]

Welcome to the forum!

Are you able to post a small sample workbook so we have data with which we can work and experiment? You can post a workbook using Go Advanced --> Manage Attachments to bring up the attachment window. Please be sure to alter or remove any sensitive data.

[vedro440]

Unfortunately, i'm unable to attach files. For example, as i filter thru fruit category in the below example, i would like to obtain medians for the categories down below. The current formula i'm using capture visible and invisible row.

Fruit Number
Apples 5
Oranges 10
Grapes 12
Apples 15
Oranges 6
Grapes 9
Apples 8
Oranges 5
Grapes 6
Apples 2
Oranges 3
Grapes 2
Apples 1
Oranges 8

Categories Median
Apples ?
Oranges ?
Grapes ?

[CAntosh]

I would advise avoiding all of the nesting IF clauses. If your data is in A2:B16 and your category filter is in D2, use the following array-formula (confirm with Ctrl + Shift + Enter instead of Enter):

=MEDIAN(IF(($A$2:$A$16=$D2)*($B$2:$B$16>0),$B$2:$B$16))

Try experimenting with the attachment to see if it's working as desired, then adjust the ranges for your actual data: