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

#### mrajotte

##### New Member
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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### mikerickson

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member
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?

##### MrExcel MVP
...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

Replies
2
Views
153
Replies
1
Views
207
Replies
4
Views
5K
Replies
16
Views
2K
Replies
6
Views
702

1,136,776
Messages
5,677,666
Members
419,711
Latest member
dacrmcvega0

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back