How to Have Cells Sum'ed Even if a Cell is Blank

[ablackwell1205]

Hi! So I am summing the numbers in the picture below. The number populates based on an =IF function based on the cell before it. With the "N/A" in the cell before it, the cell returns "". When I then try to sum these 4 cells, it gives #VALUE! since my one cell is blank. Is there a way to have Excel ignore if the cell is blank and just sum the values?

[#2]

@ablackwell1205 

I presume you mean SUM does not work because one of the cells has a #VALUE! error.

If you'd like to sum and ignore errors, you can use AGGREGATE.

=AGGREGATE(9,6,A1,C1,E1,G1)

[#3]

@ablackwell1205 

SUM() ignores text. Always has. Always will.

[#4]

@ablackwell1205 Hi , You can use subtotal formula to get rid of this issue. 

Please find the below sheet of solution FYR