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 w...
excel
Formulas and Functions
  • Subodh_Tiwari_sktneer's avatar
    Subodh_Tiwari_sktneer
    Oct 07, 2020

    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))

     

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