Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# Subtotal Ignoring error

Posted on 2011-03-15
Medium Priority
578 Views
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)"
0
Question by:Ruchi_Sas
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 85

Expert Comment

ID: 35136724
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))"
``````

0

LVL 6

Accepted Solution

akajohn earned 500 total points
ID: 35136733
Try

=SUMIF(9,R[3]C:R[7128]C,"<>#N/A")
0

LVL 19

Expert Comment

ID: 35136761
you could try using matrix formulae, this way you could use the ISNA function :

``````=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
0

LVL 19

Expert Comment

ID: 35136764
then replace A1:A6 with R[3]C:R[7128]C
0

LVL 50

Expert Comment

ID: 35136796
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

LVL 85

Expert Comment

ID: 35136797
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
###### Suggested Courses
Course of the Month12 days, 3 hours left to enroll