Sum Every Other Cell -Ignoring Errors in Range

[dwgnome]

Hi,

I am unable to come up with a simple formula that will sum every other cell in a row AND ignore any error messages that may occur. Numbers are always positive.

For this case:

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=640 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=80><TBODY><TR style="HEIGHT: 12.75pt" height=21><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=80 height=21></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>C </TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>D</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>E</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>F</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>G</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>H</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=80>I</TD></TR><TR style="HEIGHT: 15pt" height=25><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=25 x:num>8</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" x:num>2</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>3 </TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" x:fmla="=8/0" x:err="#DIV/0!">#DIV/0!</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>5</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" x:num>6</TD><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" x:num>7</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ffcc99" x:num>8</TD></TR></TBODY></TABLE>

I would like to be able to add numbers that are shaded (ignore any text, blanks or error messages).

I have this formula that works if only numbers are present:

=SUMPRODUCT(((MOD(COLUMN($C$8:$I$8)-COLUMN($C$8),2)=0))*(($C$8:$I$8)))

I also would like to know if any of the formulas below can be modified to accomplish my goal as well.

=SUM(SUMIF($C$8:$I$8,{"<0",">0"}))

=SUMIF($C$8:$I$8,"<=9.99999999999999E+307")

Your input would be appreciated.

 

[barry houdini]

Is there a way of identifying the columns to sum other than those being every other column, e.g. a row with text value that are the same in every other column? - that would make things easier, otherwise try

=SUM(IF(MOD(COLUMN($C$8:$I$8)-COLUMN($C$8),2)=0,IF(ISNUMBER($C$8:$I$8),$C$8:$I$8)))

confirmed with CTRL+SHIFT+ENTER

 

[dwgnome]

Thank you Barry, that solution worked.

As far as header names, the ones that need to be summed are called Rec.#45-1 Cum. Tot. ,Rec.#45-3 Cum. Tot. etc. The other columns are either Rec.#45-1 Setting or Hood 45 Pressure. So conceivably, one could look for a word "Cum." and that would differentiate enough going through the columns.

Does that help in deriving a different approach?

Is the SUMPRODUCT solution the only one or could the other SUMIF type constructs work as well?

 

[barry houdini]

The other two solutions will ignore errors but are not easily adaptable for summing every other column. Given a header in row 7 you could use a formula like this

=SUM(IF(RIGHT($C$7:$I$7,9)="Cum. Tot.",IF(ISNUMBER($C$8:$I$8),$C$8:$I$8)))

confirmed with CTRL+SHIFT+ENTER

....or with SUMIFS function in Excel 2007 or later versions

=SUMIFS($C$8:$I$8,$C$8:$I$8,">0",$C$7:$I$7,"*Cum. Tot.")

 

[Aladin Akyurek]

Thank you Barry, that solution worked.

As far as header names, the ones that need to be summed are called Rec.#45-1 Cum. Tot. ,Rec.#45-3 Cum. Tot. etc. The other columns are either Rec.#45-1 Setting or Hood 45 Pressure. So conceivably, one could look for a word "Cum." and that would differentiate enough going through the columns.

Does that help in deriving a different approach?

Is the SUMPRODUCT solution the only one or could the other SUMIF type constructs work as well?

A formula with SumIf would not succeed here, because

=SUMIF(C7:I7,"*Cum. Tot.",C8:I8)

cannot handle #DIV/0!. A formula with SumProduct wouldn't do the job
for a different reason: Usual conditionals cannot eliminate error values.
So Sum(If...)) is the best thing here.

 

[dwgnome]

Thank you both for providing another solution and explainations.

To make the header search a bit more flexible, I would like to use a partial word match. Is there a way to do that?

 

[barry houdini]

=SUM(IF(RIGHT($C$7:$I$7,9)="Cum. Tot.",IF(ISNUMBER($C$8:$I$8),$C$8:$I$8)))

This formula is already doing a partial word match (matching the last 9 characters against "Cum. Tot.").You could make that more flexible with a formula like:

=SUM(IF(ISNUMBER(SEARCH("Cum.",$C$7:$I$7)),IF(ISNUMBER($C$8:$I$8),$C$8:$I$8)))

 

[Aladin Akyurek]

Thank you both for providing another solution and explainations.

To make the header search a bit more flexible, I would like to use a partial word match. Is there a way to do that?

"*Cum. Tot." means "ends with".

"*Cum.*" means "contains".

"Rec*" means "begins with".

If you put the search string in X2, you can have:

"*"&X2

"*"&X2&"*"

X2&"*"

This type of referencing is only possible with: the serach functions like MATCH,VLOOKUP,LOOKUP, etc., CountIf, SumIf, CountIfs, SumIfs, and text functions like Search and Find.

 

[dwgnome]

Yes, that should do it. I believe you can even put other words to match in a separate cell as in a drop down box and have the formula adjust automatically to the user's choice.

Again thank you.

Looks like our messages crossed paths on that one. Thanks for the additional information.