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)


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



@leochan2003 ,,,


You may use either of these:






{=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.