Conditionnal Sum

[Aure]

Hello,

Here is what I have :

A B
2 -
4 5
5 -
6 7

What I need to do is a sum of the column A if <>#N/A" and "<>#DIV/0!
And In case the cell value B is not blank, replace the value in the corresponding A cell with the B cell value

In this case A column sum is 17 the sum I want is 19

Could anyone help?

 

[Aladin Akyurek]

Hello,

Here is what I have :

A B
2 -
4 5
5 -
6 7

What I need to do is a sum of the column A if <>#N/A" and "<>#DIV/0!
And In case the cell value B is not blank, replace the value in the corresponding A cell with the B cell value

In this case A column sum is 17 the sum I want is 19

Could anyone help?

Your sample does not cover your explanation. It lloks like you are summing column B, not A. If so:

Control+shift+enter, not just enter...

=SUM(IF(ISNUMBER(B2:B5),B2:B5,A2:A5))

assuming that there are no error values in A2:A5. Otherwise:

=SUM(IF(ISNUMBER(B2:B5),B2:B5,IF(ISNUMBER(A2:A5),A2:A5)))

 

[Aure]

Your sample does not cover your explanation. It lloks like you are summing column B, not A. If so:

Control+shift+enter, not just enter...

=SUM(IF(ISNUMBER(B2:B5),B2:B5,A2:A5))

assuming that there are no error values in A2:A5. Otherwise:

=SUM(IF(ISNUMBER(B2:B5),B2:B5,IF(ISNUMBER(A2:A5),A2:A5)))

Maybe I was not clear enough
A B
1/ 5 -
2/ 6 -
3/ 3 6
4/ 2 -
5/ 1 3
6/ 0 1
7/#N/A -
8/#DIV/0! -

What I would like is the sum of the A column excluding #DIV/0! and #N/A
But if value is entered in the B column, remplace the value the corresponding A cell with the B cell.

So instead of having 17 (5+6+3+2+1) with the sum of the column A (as if value was not entered in the B column), I want to have 23 (5+6+6+2+3+1)

Tkx

 

[jraj1106]

Hi
=SUM(IF(ISNUMBER(B1:B8),B1:B8,IF(ISNUMBER(A1:A8),A1:A8)))


You just have to change the cell range. That's all...


Aladin_Akyurek gave you the correct formula.

 

[Aure]

Hi
=SUM(IF(ISNUMBER(B1:B8),B1:B8,IF(ISNUMBER(A1:A8),A1:A8)))


You just have to change the cell range. That's all...


Aladin_Akyurek gave you the correct formula.

Indeed my fault

Tkx a lot

 

[jraj1106]

You are welcome...