How can I use "SumIF" to ignore error cells, but add up non-continuous cells?

[mrajotte]

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

 

[mikerickson]

The best (and easiest) would be to prevent those errors upstream. For example, in Q5 you could put
=IFERROR(current_formula,0)

or change the current_formula to avoid errors.

 

[mrajotte]

Thanks for the suggestion. This is a grading sheet for a course, and I can't figure out how to "fix" the error on a permanent basis without affecting how the sheet works.

There are 12 cases throughout the grading sheet where I use =AVERAGE(BT11:BU11) in cell BV11. Before the assignment is complete, there is text in each cell (BT11 and BU11) that is necessary to indicate what the particular student needs to do. I cannot replace this data with a number. Once they do the assignment, I type in the grade, and BV11 calculates. Before that, I get an error, #DIV/0!.

I also need to have a running sum of the scores for all students at any point in time, to calculate who the top 10%, bottom 10% are. My thought was to calculate the course total, and assign a percentage for my top/bottom 10, using a dummy student with the top score possible for each assignment to provide the percentage cutoffs.

Where I get stuck is trying to add the score across the entire sheet -- I can't ignore the errors.

I do not want to have to update the formulas every time I enter a new score.

Any suggestions!?!?

 

[mikerickson]

If the text in the cells is the same for every column you could use a custom Number Format to mask 0's

[=0]"FinishHomework";General

might be one custom format.

 

[mrajotte]

Unfortunately, it's different for each cell. It is actually the name of a team member that they are required to do an assignment on, so there are 42 different options for what will be in each cell.

Do you know of a way to ignore errors when summing non-continuous cells, or do they have to be continuous?

 

[Aladin Akyurek]

...Do you know of a way to ignore errors when summing non-continuous cells, or do they have to be continuous?

Contiguous (continuous) data or non-contiguous data but with a predictable lay-out easier to process even when they house errors