SOLVED

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

New Contributor

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.

4 Replies
best response confirmed by leochan2003 (New Contributor)
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))

 

@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.