[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 584
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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