Email:      Pass:    Pass?

Free Excel Forum

Sum Cells And Ignore Error Values

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am trying to sum the following cells;
However some of them have the #VALUE! in them.

So my total then ends up being the #VALUE!.

Is it possible t ignore the #VALUE! ?

Similar Excel Video Tutorials

Helpful Excel Macros

Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Delete All Chart Sheets in Excel - Only Chart Sheets are Deleted - Not Embedded Charts
- Delete all chart sheets and tabs in Excel using this macro. This will only delete the charts and graphs that are in the
Get Values from a Chart
- This macro will pull the values from a chart in excel and list those values on another spreadsheet. This will get the s
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Filter Data in Excel to Display Results that Contain 1 of 2 Possible Values - AutoFilter
- This Excel macro filters data in Excel to display results that contain 1 of 2 possible values. This macro uses the xlor

Similar Topics


Long time lurker, but hoping someone might be able to help with a problem I'm having on Excel (2007).

On one sheet I have a data table that looks like this:

Question Response Question Response
1 39 Ignore Ignore 66 Ignore Ignore 40 Ignore Ignore 71
2 39 Ignore Ignore 66 Ignore Ignore 40 Ignore Ignore 71
3 39 Ignore Ignore 66 Ignore Ignore 40 Ignore Ignore 71
4 20 Ignore Ignore 40 Ignore Ignore 23 Ignore Ignore 34
5 15 Ignore Ignore 40 Ignore Ignore 23 Ignore Ignore 34
6 39 Ignore Ignore 66 Ignore Ignore 40 Ignore Ignore 72

The table extends further in both directions. Entries marked "Ignore" are text fields present in the original data table that need to stay there...

What I hope to do is count the number of any given response codes to a particular question code and populate a table (on a different sheet) that looks like:

Response --> 1 2 3 4...nn

The variability of data structure in the original table is causing me a problem. There is no certainty that particular questions will appear in any given sequence.

What I had in mind was a function a bit like COUNTIF but that operated across an array, with a built in cell offset.

Does anyone know if/how it can be done?

Many thanks in advance! (and apologies for any initial post confusion )

PS - Attached example if this is useful: Book3.xlsx

Hi all,

I've got a problem that I'm sure has come up before, but I can't find an exact answer.

I want find the average of a number of cells (B11,B17,B23, B29, B35...) but some of the cells have #DIV/0 errors in them, so my final result also ends up as a #DIV/0 error.

I know that the best way is to go back and fix the B11, B17, B23 etc cells so that they don't have #DIV/0 errors, but that is just not feasible at this stage. There's too many of them and I need to get this work done soon.

I just want to ignore any cell that has an error in it and calculate the average based on all the cells that do have figures in it. This can't be that hard can it?

Thanks for your help.



How to average a set of values but ignore zero (0) and error values (#N/A, #value!)? Been searching for a way to do this. Lots of forums on how to ignore zero value OR ignore error values but not ignore both. Appreciate the assistance.


This is what I'm trying to do. Hopefully simple to you, but tough for me....
Calculate averages while skipping certain rows and all zero values...


A1 100
A2 200
A3 300
A4 TOTAL: 600 (ignore this row)
A5 100
A6 0
A7 200
A8 TOTAL: 300 (ignore this row)
etc.. (down)

All of these cells are linked to another workbook. For some reason it
inserts a zero by default (even if the cells in the other workbook are
blank). The goal is to calculate the averge miles traveled. I'm trying to
have the function ignore all of the zero values. I've tried two suggested
formulas, but it looks like I have too many arguments?

=(SUM(G12:G15))/(COUNTIF(G12:G15, "<>0"))

Any help would be much appreciated...

Jason K.

Hello! I'm a very frustrated beginning excel user and could use your help!

I've read the posts regarding using SumIF to ignore error cells when adding a group of cells, but when I try to use the formula I get an error. I think it's because I'm trying to add cells that are not continuous.

Here's what I tried:

=SUM(Q5,U5, X5, AF5, AK5, AN5, AR5, AV5, BA5, BD5, BH5, BL5, BS5, BX5, CA5,#N/A)

How can I fix this to add my cells, but ignore those that have errors?


I have some formulas that are just simple sums. Eg: =SUM(G4:G34) The formula is exactly correct, but still gives me a "Formula Omits Adacent Cells"

Is there anyway through VBA to tell it to ignore that error?

I don't want to turn it off altogether in error checking, because it has helped me find errors. I just want to ignore it for those cells and do so a bit more automatically if possible.

Edit: I did try recording a macro and ignoring those cells, but alas, the recording was pretty empty when it came to ignoring the error.

I have a formula in a cell(A) that reads the information from another cell(B)
using the Vlookup function. However, some of these B cells contain a formula
with no results in them, resulting in A displaying the #VALUE! error. is
there a way to ignore those cells that contain no values?

I am also trying to add up all of the A cells but obviously get the #VALUE!
as a result because of the previous errors. Anyway to count these VALUE
cells as zero or circumvent in any way?


Anyone now how to globally select "Ignore Error" when Excel so kindly tells you that your cell leaves out adjacent cells? I'm leaving those cells out intentionally but it's a long column of those little green triangles with the info pop-up that tells me I'm omitting adjacent cells and I'd rather not have to go into each individual cell to tell it to ignore the error Excel thinks I made...

So I highighted a bunch of cells where numbers are stored as text and chose "ignore error". But when I re-sort the list, the errors come right back again.

Is there a way to get them to stick so I don't have to keep choosing ignore error?


How can I make excel sum up a colum, and ignore cells shown as #I/T?
This error sign might not be the same in the english version of excel.

If I try to sum up cells with the error value, the feedback will also be #I/T.

I have uploaded a screenshot to illustrate the problem for You

(The reason for the faulty cells is that the date does not contain any values)

thanx! =)

Faulty formula is now; =(SUM(B307:B316))

A column has #n/a's as well as numbers - On Sum Total at base of column it returns #n/a - how do I ignore all #n/a's to return numeric total.

Edit - please ignore already have the answer ------

Is there a way to make a simple sum formula ignore a cell's value based on the color of the cell itself?

For example, imagine a column of 10 cells, all of whose values are 10. Apply the sum formula for 100, right? Ok, now, I change the color of 5 cells from white to red, apply the sum formula, and here's my question:

is there some sort of conditional formatting I can apply telling the sum formula to ignore the red cells, and return a total of 50?


How do I ignore the DIV/0 Error in cell F35, H35 & H35 or change the formula to fit?


******** ******************** ************************************************************************> Microsoft Excel - 03.2007 CPC Call Volume per Skill.xls ___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout C28 D28 E28 F28 G28 H28 C29 D29 E29 F29 G29 H29 C30 D30 E30 F30 G30 H30 C31 D31 E31 F31 G31 H31 C32 D32 E32 F32 G32 H32 C33 D33 E33 F33 G33 H33 C34 D34 E34 F34 G34 H34 C35 D35 E35 F35 G35 H35 =
A B C D E F G H 28 03/25/07 Sun 0 0 0.00% 0:00:00 0:00:00 0:00:00 29 03/26/07 Mon 0 0 0.00% 0:00:00 0:00:00 0:00:00 30 03/27/07 Tue 0 0 0.00% 0:00:00 0:00:00 0:00:00 31 03/28/07 Wed 0 0 0.00% 0:00:00 0:00:00 0:00:00 32 03/29/07 Thu 0 0 0.00% 0:00:00 0:00:00 0:00:00 33 03/30/07 Fri 0 0 0.00% 0:00:00 0:00:00 0:00:00 34 03/31/07 Sat 0 0 0.00% 0:00:00 0:00:00 0:00:00 35 * Total 511 0 0.00% #DIV/0! 0:06:09 0:00:57 Follow-Up *
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box


I'm looking for an easy way to tell a formula to ignore a particular cell if it contains an asterisk after the number.

For example, there could be four cells, each with a value. However, one of them has an asterisk. I don't want this cell's value included in the formula that works out the total. Also, the asterisk could be added to any one of the cells, it won't always be the same cell.


= 8 (not 12)


= 10 (not 12)

I did have it working as SUM ignored the cell, but now having another formula working on the same information I get the #VALUE error, which means I need some kind of ignore.

This is the formula I'm currently using:


Any help much appreciated.


I have a simple formula just adding up a colum of cells. The problem comes in when 1 of the cells ends upp with a negative number, my total gets thrown off.
This is my formula:

I want to use the following formula at the bottom of a column of inputs:

=IF((D14+D15)>1,((D14+D15)/-2),"")+IF((D16+D17)>1,((D16+D17)/-2),"") etc.

This formula makes sure that if various cell entries in a column are at
least 2 items in that cell, they will be counted as two per package plus a
fraction (thus the divisor /-2). As long as a) the sum of the cells is at
least two and b) there are no blank cells, the formula works. If however, one
of the cells is blank and the total is less than 2 an error message occurs
#value! . I have tried using IGNORE BLANKS in data validation but it does
not affect the error message occuring.

Anyone know if IGNORE BLANKS should work with this? I assume you select all
the cells in the column including the summation cell, true? Also, is the
option ANY VALUE the correct criteria or should I reset it to whole numbers?

Thanks to anyone watching.


My problem is that I want to add across columns, which is not so difficult but I have to "ignore" the blank cells because of the way my formula works.

I'm not sure of the correct mathematical term for the addition I have to do but here is my example:

in this row are the values

1 2 3 4 5

In the row below I want to add the numbers so the result is

1 3 6 10 15

basically, a cell is always adding itself to the result of the sum from the previous cells. This formula I can handle, (=A2+B1 then autofill the results by dragging the formula across the empty cells) but when there are blank cells between values I get the error message !value. How can I write a formula which will ignore the empty cells?

good day i should know this formula but for some reason i can't remember. need to average some cells but ignore the dreaded #div error


A25 is 25
A50 is 30
A75 is #div/0!
A100 is 30

so i need to get the average of those but ignore the #div/0!

those cells are a result of an average of a range (a1:a25 etc) so the formula could also be in that range as long as the formula the get the overall average doesn't count in any 0's.

I've created a document that has a different worksheet/tab for each month of the year. Then I have a summary page that pulls data from each worksheet. However, because some cells are not populated until that month is current, I get the #DIV/0! error. Is there a way to ignore unpopulated cells?

I was trying to write data from Excel to an output file. It works fine, except if there are no values in a cell with an embedded formula. Is there VB code that I could add to the macro, so that it would ignore the #N/A error in the cell and continue writing data from cells that contain data only? thanks

I am making a spreadsheet with survey results being displayed in columns. The last column calculates the average answers, some cells in the middle calculate the average of a group of surveys. Some of the data is not entered because the results are not in, or the event has not taken place yet. This leaves me with a #div/0! error in some of the cells how do I ignore that, or set the cells with that error to not do anything if it is dividing by zero.


I have a column of data that links to another sheet I need a ytd total for.
The problem is that the cells contain formulas (on the feeding sheet) that
return #div/0! until the date has passed and data is entered into its
referring cells.

Is there a way I can sum these columns and ignore the #div/0! 's ? I had
been using a simple a20+a40+a60 type formula.



i have a lot of cells in my worksheet in which numbers are stored as text.
So , it shows an error.
Presently i am going to the cell which shows the error and ignoring it one by one.I want to ignore this error in the cells having this error in my complete worksheet in one go.Is this possible?

Hi There, anybody knows if it's possible to ignore 0 values in a data range for a chart?

I'm using a pivot table and were able to ignore/suppress the zero values (using filter or IF NA() function). However, in my Excel Pivot Chart all of the x-values are shows, although there are no y-values for those fields (or 0 values). This leads to a chart with very much x-points with value 0.

I want the Pivot chart to ignore the x-range with 0 of NA values in my chart, but cannot figure out how to do that.

Can anybody help me out?
Thanks a lot for your help,

Kind regards,



I have some cells that have sort codes in them but for some reason it is recognising certain ones as text and therefore converting them into date i.e 01-01-10 has a green flag in the top left corner and always converts into a date. Now if I click on the exclamation mark and ignore error, it fixes if, however, I have thousands of lines like this. So my question is - how do I format these cells without having to click and select ignore error on them all?

Thanks in advance.