Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!

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! ?

View Answers     

Similar Excel Tutorials

Introduction to Using Filters to Refine Data in Excel
Filtering allows you to hide rows of data which you are not interested in so that you can easily look at the rows y ...
Error bars in Charts in Excel
How to add, manage, and remove error bars in charts in Excel. Error bars allow you to show the potential variance ...
Prevent Duplicate Values in Excel
I show you how to prevent duplicate values being entered into Excel using Data Validation. Let's say we have a lis ...
Error Values in Excel - Full Explanation
Here, I'll teach you what the errors in Excel mean.  There are many errors that you can get and each one means som ...

Helpful Excel Macros

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
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
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?



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.


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 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.


I have a sheet I need to do that will add up somebodies working hours.

The idea is that the staff can look at the sheet to check their forthcoming duties. To that end they need to be able to see 0700-1900. I have started by creating two columns for each day.. start time finish time. I have then in the next columm put a formula to calculate the total hours. This will ultimately be hidden to allow ease of viewing for the staff.

At the end of the row I want a total that will pull the information from the hidden cells (i.e 7 hidden cells for one week) and give me the total hours.

If somebody has a day off during this shift pattern then instead of a time entry it will simply show RD. This causes an expected error of #VALUE!

The RD bit could occur at any day of the week and so I need a formula that will look at all 7 hidden cells and return the total hours ignoring anything that is not a time.. i.e ignore all the #VALUE! cells.

Possible? Thanks in advance.

OK, I asked here before on how to ignore a range of cells that are empty in ranking so it will not return an error. We got it fixed but I just found noe that if I put zero"0" in one of the cells to rank, it will also rank all other blank cells as "0" thereby resulting in all blank cells with same value.

How do I ignore the blank cells? Here is my formula?


Hello everyone, I trying to make my Average() array formula ignore months, which have more than 5 blanks to simply ignore it, does anyone have a clue how to do this?
In the example that I show you, the second month should be excluded, because the values in column R have too much blank cells in that month.

If using the blanks is too difficult, values in column N below 5 would respond in the same way (if there more than 5 cells with values less than "5").