How to write a formula to sum cells with certain criteria?

[leochan2003]

Dear all,

I would like to sum cells that are not blank to fulfill the following criteria:

1. when the sum range is blank, then blank

2. when the sum range equals to zero, then 0

Your help would be very much appreciated.

[#2 · Accepted solution]

@leochan2003 

If you want to sum the range say A2:A10, you may try something like this...

=IF(COUNTA(A2:A10)=0,"",SUM(A2:A10))

[#3]

@leochan2003 ,,,

You may use either of these:

=IF(NOT(COUNTA(A1:A10)),"",SUM(A1:A10))

{=IF(ISBLANK(A1:A10),"",IF(A1:A10=0, 0,SUM(A1:A10)))}

Note: Second formula is as array (CSE) formula, so finish it with Ctrl+Shift+Enter.

How it works:

1. If All cells in range A1:A10 are BLANK then returns blank.
2. If All cells in Range A1:A10 has ZERO then returns zero.

Otherwise gets the SUM.

[#4]

Thank you so much.
@Subodh_Tiwari_sktneer 

[#5]

You're welcome @leochan2003!