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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,793
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
22,793
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,138
...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,082,126
Messages
5,363,315
Members
400,725
Latest member
excelingtolearn

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top