Ruchi_Sas
asked on
Subtotal Ignoring error
I need to subtotal a column ignoring NA# Values in VBA
I am currently using ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[3]C:R[7128] C)"
I tried to use Iserror and ISNA , but receiving error. Please help!
I am currently using ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[3]C:R[7128]
I tried to use Iserror and ISNA , but receiving error. Please help!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you could try using matrix formulae, this way you could use the ISNA function :
a formula is turned into a matrix formula by pressing [Ctrl]-[Shift]-[Enter] instead of just [Enter] when typing the formula.
When clicking on a cell containing a matrix formula, the formula itself is presented with enclosing { } brackets
=SUM(IF(ISNA(A1:A6); 0; A1:A6))
a formula is turned into a matrix formula by pressing [Ctrl]-[Shift]-[Enter] instead of just [Enter] when typing the formula.
When clicking on a cell containing a matrix formula, the formula itself is presented with enclosing { } brackets
then replace A1:A6 with R[3]C:R[7128]C
Which version of Excel are you using? In Excel 2010 you can use AGGREGATE function to subtotal and ignore errors, e.g.
=AGGREGATE(9,3,A1:A10)
regards, barry
=AGGREGATE(9,3,A1:A10)
regards, barry
Note: I assumed you were using Subtotal in order to allow filtering. If that is not the case, then you could just use:
ActiveCell.FormulaR1C1 = "=SUMIF(R[3]C:R[7128]C,""< >#N/A"")"
which is basically a correction of what akajohn said.
ActiveCell.FormulaR1C1 = "=SUMIF(R[3]C:R[7128]C,""<
which is basically a correction of what akajohn said.
Open in new window