Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 602
  • Last Modified:

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!
0
Ruchi_Sas
Asked:
Ruchi_Sas
1 Solution
 
Rory ArchibaldCommented:
Try:
activecell.FormulaArray = "=SUM(IF(SUBTOTAL(2,OFFSET(R[3]C:R[7128]C,ROW(R[3]C:R[7128]C)-MIN(ROW(R[3]C:R[7128]C)),,1)),R[3]C:R[7128]C,0))"

Open in new window


0
 
akajohnCommented:
Try

=SUMIF(9,R[3]C:R[7128]C,"<>#N/A")
0
 
Arno KosterCommented:
you could try using matrix formulae, this way you could use the ISNA function :

=SUM(IF(ISNA(A1:A6); 0; A1:A6))

Open in new window


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
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Arno KosterCommented:
then replace A1:A6 with R[3]C:R[7128]C
0
 
barry houdiniCommented:
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
0
 
Rory ArchibaldCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now