You are here: HomeExcelTutorials

# Subtotal range of values that include errors

## Subtotal range of values that include errors

Price: FREE

Please would someone tell me the syntax of a formula to return the total of values of numerical entries in a range of cells, some of which cells individually return the error state #N/A? Ideally I would like to use SUBTOTAL(9,.) rather than SUM() but I can live with SUM() if necessary.
I have tried
=SUM(range)
=SUMIF(range,NOT(#N/A))
=SUMPRODUCT(range,NOT(ISNA(range))
=SUMPRODUCT(range*NOT(ISNA(range))
These all return #N/A as the total. This is not desired. I want the #N/A values to be ignored in the summing process.

Visit publisher's web-site: Subtotal range of values that include errors

Category and Tags: ExcelTutorials :

## Related Tutorials

### Subtotal ignoring Error Values

Free: Yes
Tags: Excel ›

I'm searching for a subtotal formula, which can ignore errors.
For regular sums I use a formula like this: "{=SUM(IF(ISNUMBER
(Data),Data))},
which excludes errors.
Does anyone have an idea how I can apply this to a subtotal operation?

### How to Subtotal values that begin with a letter?

Free: Yes
Tags: Excel ›

I have an issue with Excel 2007.
I am able to use the subtotal function to get a dealer number for a column. If the dealer number begins with a C, my subtotal function errors with the #DIV/0!
In my sample spreadsheet, if you filter just one dealer number, the formula in A18 will work. If you switch to the last dealer C1221 it errors because of the letter. Any help would be appreciated or other methods to get this done.

### Subtotal past errors

Free: Yes
Tags: Excel ›

This array works nice:
=SUM(IF(NOT(ISERROR(E1:E4)),E1:E4))
But what if you want a SUBTOTAL?

### SUBTOTAL to ignore #N/A's

Free: Yes
Tags: Excel ›

I don't know if this is even possible, but.
I'm working with Excel 2003 and am looking for a SUBTOTAL formula that ignores errors. It has to be a SUBTOTAL as the data is filtered and I need the subtotal to reflect the filtered changes.
I've tried to search the forum history and the Internet in general for a solution but with no real success.
I know I could just filter on the errors and remove them, but a formula that simply ignores them would be much less time consuming.

### Include Subtotal in SumIf

Free: Yes
Tags: Excel › Functions

I would need to sum a range of cells that match a certain condition (SUMIF), but I would need it to work as well being limited by the autofilter. anyone

## Related Applications & Scripts

### JS Error Log

Free: No

JS Error Log is a simple set of PHP classes and jQuery plugin which allows log and store all JS errors as PHP errors.
Features
- Write all JS errors to CSV files located on server,
- Can Log data:
- - date, time, error message, error URL , error line number, client IP, client host name, client user-agent, Send errors to mailbox, Documentation included.

### ASP Include File

Free: Yes
Tags: ASP › Excel Server Side Includes

Although it is possible to use include files in ASP, I found that it was not possible to do so during runtime. VBScript seems to be lacking an include function. So I wrote these 3 functions: asp_html_include(), asp_vbs_include(), and vbs_include(). The reason for this is that I could not create a function that executed VBS AND displayed HTML.

### Fix Windows Errors & Optimize Your System

Free: No
Tags: Software › Excel Performance

Fix Windows Errors & Optimize Your System: No Blue Screen, No Lock up, No Errors, Less Garbage Files More Smooth System Running.
All in one PC Care Can:
- Registry Clean
- Optimize PC
- Fix Error
- Fix DLL
- Disk Defrag
- Windows Update

### Key Generator

Free: No
Tags: ASP.NET › Excel Random Text

The KeyGenerator is a .NET component for .NET applications that allows to developers generate random values using the combination of numbers, letters, characters and custom values.
Principal features:
- Get random values using Numbers, Letters, Numbers & Letters, Numbers-Letters-Characters or custom values defined by you
- Include source code so you can add custom elements
- It is compatible with C# and VB projects (include demo apps for both)
- It is compatible with different types of .NET projects (web, desktop, console)

### Log Digester: Error Log Analyzer

Free: Yes
Tags: PHP › Excel Debugging

It is highly recommended that you log your errors to a file instead of displaying them in the browser. As this log file grows, it can become difficult to find and react to errors. This script will read and analyze your php error log and spit out a more readable version with the worst errors highlighted so you can fix those first.