Solved

# I need an excel formula

Posted on 2011-10-18
138 Views
I need a SUM formula to look at E1:E400 and ignore "#N/A" and "#VALUE!" errors and sum the rest of the valid numbers in that column.
0
Question by:wrt1mea

LVL 50

Accepted Solution

Try this

=SUMIF(E1:E400,"<9.99E+307")

regards, barry
0

LVL 1

Author Comment

It works! Not sure how...care to explain?
0

LVL 1

Author Closing Comment

Works perfectly!
0

LVL 50

Expert Comment

SUMIF ignores errors in the sum range so you just have to set the criteria so that all numbers are summed.

If you only have positive numbers in that range it would be sufficient to use

=SUMIF(E1:E400,">0")

but in the version I used 9.99E+307 is a very large number (almost the biggest you can get in Excel), so SUMIF sums everything that's less than that, should be any number including negative numbers

regards, barry
0

LVL 1

Author Comment

Lesson for the day...as always, thanks for the help!
0

## Featured Post

### Suggested Solutions

Excel file corrupted. 13 28
VBA Help TT V.1 9 28
VBA Help TT V-1.1 15 22
Excel VBA, find a string in a column, update a cell 7 22
What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.