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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

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
36,969
Office Version
  1. 2016
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
36,969
Office Version
  1. 2016
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,356
Messages
5,635,791
Members
416,882
Latest member
ericvrealty

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