EXCEL1: If is number and Sum formula

[aacod]

I want a sum formula in cell I13, such that if there is a number in cell B13, D13,F13,H13, put total of B13, D13, F13, H13 in I13 or leave it blank.

Thanks.

 

[Tyny8]

This might work. =IF(AND(ABS(B13)>0,ABS(D13)>0,ABS(F13)>0,ABS(H13)>0),SUM(B13,D13,F13,H13),"")

This assumes that there has to be a number in B13, D13, F13, and H13, otherwise it will just leave it blank. It also assumes your number can't be zero.

 

[p45cal]

=if(count(b13,d13,f13,h13)=4,sum(b13,d13,f13,h13),"")

 

[aacod]

p45cal,

Your formula works, if there is a figure in b13, d13,f13,h13. If there is no figure in one of the cells it does not Total, where as I want it to total even if one of the cell is blank. Any suggestion.

Thanks.

aacod

 

[aacod]

Tyny8,

Your forfumla works perfect.

One more thing:

I want a formula in B13 such that if there is a number from B7:B12, sum it in cell b13 otherwise leave it blank.

Thanks.

aacod

 

[Rick Rothstein]

p45cal,

Your formula works, if there is a figure in b13, d13,f13,h13. If there is no figure in one of the cells it does not Total, where as I want it to total even if one of the cell is blank. Any suggestion.

What about this formula then...

=IF(B13&D13&F13&H13="","",B13+D13+F13+H13)

One more thing:

I want a formula in B13 such that if there is a number from B7:B12, sum it in cell b13 otherwise leave it blank.

Give this formula a try...

=IF(COUNT(B7:B12),SUM(B7:B12),"")

 

[aacod]

Thanks Rick, your formula works perfect.

Now, I want a formula in B13 such that if there is a number from B7:B12, sum it in cell b13 otherwise leave it blank.

aacod

 

[Rick Rothstein]

Thanks Rick, your formula works perfect.

Now, I want a formula in B13 such that if there is a number from B7:B12, sum it in cell b13 otherwise leave it blank.

Refresh your view of this thread... I edited my original message (#6) to include a formula for that question as well.

 

[p45cal]

p45cal,

Your formula works, if there is a figure in b13, d13,f13,h13. If there is no figure in one of the cells it does not Total, where as I want it to total even if one of the cell is blank. Any suggestion.

Thanks.

aacod

change
=4
to
>0
or remove
=4
altogether, replacing it with nothing.

 

[aacod]

=IF(COUNT(B7:B12),SUM(B7:B12),"")

The above formula works perfect, but the previous formula, below

=IF(B13&D13&F13&H13="","",B13+D13+F13+H13)

puts #VALUE in Cell I13 when there is/are no figure(s) in column H7:H13.