Forum Discussion

leochan2003's avatar
leochan2003
Copper Contributor
Oct 07, 2020
Solved

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

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.

excel
Formulas and Functions

4 Replies

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Oct 07, 2020

    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.

Resources