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

mrajotte

New Member
Joined
Nov 8, 2010
Messages
4
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? :confused:

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
Joined
Jan 15, 2007
Messages
24,053
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
Joined
Nov 8, 2010
Messages
4
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
Joined
Jan 15, 2007
Messages
24,053
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
Joined
Nov 8, 2010
Messages
4
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
...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
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top