Summing numbers even if they don't exist

Do you mean?

Formula:

Please Login or Register  to view this content.

What formula are you using?

If I enter =SUM(A1:A3) with the values you suggest, i get a result of 7, which is correct, I believe?

When you say "empty", please can you clarify. Your formula should cope with an empty/null cell. However, if it has a space or a non numeric character it won't.

	A	B	C
1	2	#VALUE!	=A1+A2+A3
2	x	7	=SUM(A1:A3)
3	5	7	=N(A1)+N(A2)+N(A3)
4		7	=SUMPRODUCT(--(ISNUMBER($A$1:$A$3)),($A$1:$A$3))

Regards, TMS

Yes.

Big difference between using =A1+A2+A3 and =SUM(A1:A3)

The latter ignores text values in the range; the former does not.

I can only imagine that your "blanks" in the range are not "genuine" blanks, but perhaps the "" (which are technicallly text) as a result of e.g. formulas in those cells.

Regards

Added Fotis's formula as another option:

	A	B	C
1	2	#VALUE!	=A1+A2+A3
2	x	7	=SUM(A1:A3)
3	5	7	=N(A1)+N(A2)+N(A3)
4		7	=SUMPRODUCT(--(ISNUMBER($A$1:$A$3)),($A$1:$A$3))
5		7	=SUMIF(A1:A3,"<>")

Regards, TMS

Thanks for the rep



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.