Solved

Subtotal Ignoring error

Posted on 2011-03-15
6
549 Views
Last Modified: 2012-05-11
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
Comment
Question by:Ruchi_Sas
6 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
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))"

Open in new window


0
 
LVL 6

Accepted Solution

by:
akajohn earned 125 total points
ID: 35136733
Try

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

Expert Comment

by:akoster
ID: 35136761
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 19

Expert Comment

by:akoster
ID: 35136764
then replace A1:A6 with R[3]C:R[7128]C
0
 
LVL 50

Expert Comment

by:barry houdini
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

by:Rory Archibald
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Formula 5 45
Office 2016 without internet 6 38
Excel - conditional formatting on several columns 9 35
formatting - number format 2 14
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question