 SOLVED

Highlighted

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

4 Replies
Highlighted
Best Response confirmed by leochan2003 (New Contributor)
Solution

# Re: How to write a formula to sum cells with certain criteria?

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

# Re: How to write a formula to sum cells with certain criteria?

@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

# Re: How to write a formula to sum cells with certain criteria?

Thank you so much.
@Subodh_Tiwari_sktneer

Highlighted

# Re: How to write a formula to sum cells with certain criteria?

You're welcome @leochan2003!