Using SUM function with #N/A in some cells

[Jeeper]

Hi all

I am really stuck in solving this problem and would be grateful for any
help.

I have a column of cells in which some cells contain text, some values
and some the results of calculations. i.e.

B2 contains heading text
B3 contains A Name (The result of a VLOOKUP)
B4 contains 11452
B5 contains 12.05
B6 contains 8 (the result of a calculation)
B7 contains heading text
B8 is blank (awaiting result of a VLOOKUP)
B9 is blank (awaiting input)
B10 is blank (awaiting input)
B11 contains #N/A (Awaiting the result of a calc using data from B7,B8
and B())

This pattern continues in blocks of 5 cells to R63 in which I want to
use a function to total the contents of cells B6, B11, B16, B21 etc.
Since some of these cells contain calculated numeric values and some
contain #N/A (still awaiting their content to be calculated, as
sporting events take place) a SUM function or a =B6+B11+B16+B21 results
in another #N/A error.

Would be grateful to know how I can add this column of selected
non-adjacent cells in which there is a number containing #N/A. I need
the sum of the valid numbers without the need to be selective at the
time. I have approx 40 such columns needing a snap shot total every 15
mins.

with best regards

 

[Duke Carey]

Try

=SUM(IF(ISNA((B6, B11, B16, B21) ),0,(B6, B11, B16, B21) ))

as an array formula, committed with Cntrl-Shift-Enter
===============================

Anybody else getting blocked with the

"We're sorry, We are unable to service your request message"

It's getting REALLY irritating.

 

[Jeeper]

Duke

Many thanks for your prompt response. I tried your suggestion but,
unfortunately, without success. I still ended up with #N/A if any
cells in those to be added contained a #N/A. I tried to simplify the
prob into a small test as follows:

B6 6
B11 6
B16 6
B21 6
B22 Your suggested array formula

This worked OK and the result in B22 was 24 as expected . However If I
changed cell B21 such that it contained #N/A the test failed and the
result was #N/A in B22.

Woudl still appreciate any other ideas or things to try. Again many
thanks for your suggegstion.

with regards

 

[MrShorty]

How about removing the N/A's where they occur?? I'm thinking somethin
like =IF('condition that causes N/A is true',0,'function') in each o
the cells to be summed (B6,B11,B16,...

 

[Aladin Akyurek]

Duke

Many thanks for your prompt response. I tried your suggestion but,
unfortunately, without success. I still ended up with #N/A if any
cells in those to be added contained a #N/A. I tried to simplify the
prob into a small test as follows:

B6 6
B11 6
B16 6
B21 6
B22 Your suggested array formula

This worked OK and the result in B22 was 24 as expected . However If I
changed cell B21 such that it contained #N/A the test failed and the
result was #N/A in B22.

Woudl still appreciate any other ideas or things to try. Again many
thanks for your suggegstion.

with regards

If a large range is involved and every 5th cell is of interest...

=SUM(IF(ISNUMBER((MOD(ROW(B4:B300)-ROW(B6)+0,5)=0)*(B4:B300)),B4:B300))

which must be confirmed with control+shift+enter.

 

[Jeeper]

Hi Duke, MrShorty, Aladin

With your help I managed to fix my problem successfully. I finally
added a
=IF(ISBLANK(B5etc),0,'function) to the front end of my function in B6
(and similar cells) and everything worked. Can now add B6,B11,B16 etc
with straight forward formula. Had a slight addition problem because I
already had seven levels of nesting, but managed to overcome that

Many thanks for your kind input, you all got me thinking around the
prob and into some unfamiliar ways of using some functions.

my best regards