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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 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.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,219,033
Messages
6,145,887
Members
450,654
Latest member
lorento

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