EXCEL1: If is number and Sum formula

aacod

Well-known Member
Joined
Mar 20, 2009
Messages
667
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.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Tyny8

New Member
Joined
Mar 8, 2013
Messages
5
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.
 

aacod

Well-known Member
Joined
Mar 20, 2009
Messages
667
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

Well-known Member
Joined
Mar 20, 2009
Messages
667

ADVERTISEMENT

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
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,881
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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),"")
 
Last edited:

aacod

Well-known Member
Joined
Mar 20, 2009
Messages
667

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,881
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 10, 2009
Messages
4,763
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.
 
Last edited:

aacod

Well-known Member
Joined
Mar 20, 2009
Messages
667
Code:
=IF(COUNT(B7:B12),SUM(B7:B12),"")
The above formula works perfect, but the previous formula, below
Code:
=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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,954
Messages
5,834,570
Members
430,297
Latest member
xa_gta

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
Top