Sum Every Other Cell -Ignoring Errors in Range

dwgnome

Active Member
Joined
Dec 18, 2005
Messages
441
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.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
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
 
Upvote 0
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?
 
Upvote 0
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.")
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
=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)))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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
Back
Top