Sum Cells And Ignore Error Values

Hi,

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:

A B C D E F G H I J
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
Q
1
2
3
4
n

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

Regards,
klchoy

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.

Cheers

Trevor

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

(Example)

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

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

Any help would be much appreciated...

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

Thanks!!
Michelle