Excel formula - sum 0 only if values sum up to 0, otherwise blank?

0
votes

I've searched all over but I can't find any examples similar to mine. I'm working with an Excel-like JavaScript control (that is compatible with Excel formulas), and I need to be able to write a formula that differentiates between sums that are null (a valid use case) and actual sums that amount to 0.

For instance, you can see the highlighted row below is the sum row. By using JavaScript, I am able to check if a sum is null or actually 0 and assign the values appropriately.

enter image description here

However, I am unable to replicate this behavior in the Excel-like JavaScript control, because we use formulas, and all formulas must evaluate to a value, rather than null. The sum equals 0 when values are empty:

enter image description here

The JavaScript code is littered with formulas like the one below, which is setting the sum row equal to the sum of a range:

sheet.getCell(i, 3).formula("=(D4+D5+D6+D7+D8+D9)");

How can I modify this formula to only return 0 if the sum is legitimately 0, and an empty string otherwise?

2
excelexcel-formula
Have you trying using ISBLANK? E.g. =IF(AND(ISBLANK(D4),ISBLANK(D5),ISBLANK(D6),ISBLANK(D7),ISBLANK(D8),ISBLANK(D9)),"",SUM(D4:D9)) – CactusCake

2 Answers

4
votes

This returns the sum if there is a number in at least one cell of D4:D9:

=IF(COUNT(D4:D9),SUM(D4:D9),"")

This returns the sum if there is a number in all cells D4:D9:

=IF(COUNT(D4:D9)=ROWS(D4:D9),SUM(D4:D9),"")
1
votes

you can try this one, supposing that you want the sums on the first row, and your data starts from column C, so you put this formula in C1 :

=IF(LEN(C2:C99)=0,"",SUM(C2:C99))


this is an array formula, so press ctrl+shift+enter to calculate the formula. Then drag and copy to the right to calculate the other columns.