"Median" and "SUM" ignoring filtered cells and errors...

[eugchen]

i use =AGGREGATE(12,1,C4:C1247) for median data results on filtered cells only. but i also have cells with errors. how do i change the formula to correct for both?

CELL C3 is the cell in question



and for total sum, i use =SUBTOTAL(9,C4:C13) for sum results, and it ignores filtered cells. but it wont ignore errors. how do i do that?

[FlameRetired]

For cell c3 try

Please Login or Register  to view this content.

and for cell C2 try

Please Login or Register  to view this content.

They both worked at my end.

[bebo021999]

Sum:
=aggregate(9,3,c4:c1427)

median
=aggregate(12,3,c4:c1427)

[eugchen]

thanks guys.

interesting that
=aggregate(9,3,c4:c1427) and
=aggregate(9,7,c4:c1427)

both yield the same results. why is this?

[wk9128]

Different between 3 and 7
3. Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
7. Ignore hidden rows and error values

AGGREGATE(function_num, options, ref1, [ref2], …)
1. AVERAGE
2. COUNT
3. COUNTA
4. MAX
5. MIN
6. PRODUCT
7. STDEV.S
8. STDEV.P
9. SUM
10. VAR.S
11. VAR.P
12. MEDIAN
13. MODE.SNGL
14. LARGE
15. SMALL
16. PERCENTILE.INC
17. QUARTILE.INC
18. PERCENTILE.EXC
19. QUARTILE.EXC

Options

0. or omitted,Ignore nested SUBTOTAL and AGGREGATE functions
1. Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions
2. Ignore error values, nested SUBTOTAL and AGGREGATE functions
3. Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
4. Ignore nothing
5. Ignore hidden rows
6. Ignore error values
7. Ignore hidden rows and error values