I'm trying to put together a formula that sums 3 cells, but if they're blank I don't want to sum them.
If there's no values in the cell, it gives me "0" as a result, but I only want to have a "0" if the cells value are actually "0" and not blank.
As an example i used this formula:
=IF(COUNTA(J6,J7,J8)<3,"",SUM(J6,J7,J8))It's not giving me a result until I fill the 3 values, and I want it to sum it even if I only have 2 values.
Any tips?
3 Answers
Your condition results in the SUM not being calculated unless you have values in all three cells.
=SUM(J6,J7,J8)Will do the job just fine no matter the number of cells filled in. If you really don't want a 0 when none of the cells have a value, use:
=IF(COUNTA(C4,E4,G4)=0,"",SUM(C4,E4,G4))Try this formula:
=IF(AND(ISBLANK((J8)),ISBLANK(J7),ISBLANK(J8))=TRUE,"",SUM(J6:J8))I've solved the issue using an Array formula with different approach.Where the Formula checks all the possibilities.
N.B. Check the Caption with each Screen Shot for better understanding.
Finish this Formula with Ctrl+Shift+Enter.
{=IF(ISBLANK($A$1:$A$3),0, IF(AND(ISBLANK(A1),ISBLANK($A$1:$A$3)),"",SUM($A$1:$A$3)))}Note:
- First part of the Formula
=IF(ISBLANK($A$1:$A$3),0,works for all Blank Cells(Situation 5). IF(AND(ISBLANK(A1)with further combination prevents Excel to return Blank if the First Cell is Blank (Situation 2).
