SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1752191%22%20slang%3D%22en-US%22%3EHow%20to%20write%20a%20formula%20to%20sum%20cells%20with%20certain%20criteria%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752191%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20sum%20cells%20that%20are%20not%20blank%20to%20fulfill%20the%20following%20criteria%3A%3C%2FP%3E%3CP%3E1.%20when%20the%20sum%20range%20is%20blank%2C%20then%20blank%3C%2FP%3E%3CP%3E2.%20when%20the%20sum%20range%20equals%20to%20zero%2C%20then%200%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20help%20would%20be%20very%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1752191%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752298%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20write%20a%20formula%20to%20sum%20cells%20with%20certain%20criteria%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752298%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F822810%22%20target%3D%22_blank%22%3E%40leochan2003%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20want%20to%20sum%20the%20range%20say%20A2%3AA10%2C%20you%20may%20try%20something%20like%20this...%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(COUNTA(A2%3AA10)%3D0%2C%22%22%2CSUM(A2%3AA10))%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752657%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20write%20a%20formula%20to%20sum%20cells%20with%20certain%20criteria%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752657%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F822810%22%20target%3D%22_blank%22%3E%40leochan2003%3C%2FA%3E%26nbsp%3B%2C%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EYou%20may%20use%20either%20of%20these%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(NOT(COUNTA(A1%3AA10))%2C%22%22%2CSUM(A1%3AA10))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%7B%3DIF(ISBLANK(A1%3AA10)%2C%22%22%2CIF(A1%3AA10%3D0%2C%200%2CSUM(A1%3AA10)))%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3ENote%3A%26nbsp%3B%3C%2FSTRONG%3ESecond%20formula%20is%20as%20array%20(CSE)%20formula%2C%20so%20finish%20it%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter.%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EHow%20it%20works%3A%3C%2FSTRONG%3E%3C%2FP%3E%3COL%3E%3CLI%3EIf%20All%20cells%20in%20range%20A1%3AA10%20are%20BLANK%20then%20returns%20%3CSPAN%3Ebl%3C%2FSPAN%3Eank.%3C%2FLI%3E%3CLI%3EIf%20All%20cells%20in%20Range%20A1%3AA10%20has%20ZERO%20then%20returns%20zero.%3C%2FLI%3E%3C%2FOL%3E%3CP%3EOtherwise%20gets%20the%20SUM.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1756638%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20write%20a%20formula%20to%20sum%20cells%20with%20certain%20criteria%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1756638%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much.%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
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
Highlighted
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))

 

Highlighted

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

Highlighted
Highlighted