Exclude cells from a formula using a whole column

[Walky]

Hi,

I'm trying to have the total of a column displayed on the 2nd row (below the title of that column). Since I'll be regularly adding numbers (downwards) I decided to display the total at the top.

So I need to sum the column (as long as I enter a number is should be added without having to extend the formula's range), EXCLUDING rows 1 & 2.

How do I define that?

Thanks,

W.

 

[Walky]

Fairwinds, your new formula works only if I keep the hidden ZERO option I mentioned earlier for use with Onlyadrafter's formula.

This might sound quite newbie, but isn't there a way to define cells to exclude from this formula "sumc:c" so it sums the whole column excluding the cells used for titles and the one displaying the result?

W

 

[fairwinds]

I don't understand what you mean. Can you post your sheet?

What is really the reason that you cannot use =SUM(A3:A65536)?

 

[Walky]

I won't keep bugging you with this, since the simple formula works.
But I was intrigued (getting obsessed) by the "exclusion" option.

Thanks yall.

W.

 

[fairwinds]

Well, I would not mind helping you further but I just cannot replicate your problem. Anyway, my advice is that you use the simplest possible sollution, that is normally the best. Just let the SUM formula point at the cells you want to sum.

 

[Aladin Akyurek]

Fairwinds, your new formula works only if I keep the hidden ZERO option I mentioned earlier for use with Onlyadrafter's formula.

This might sound quite newbie, but isn't there a way to define cells to exclude from this formula "sumc:c" so it sums the whole column excluding the cells used for titles and the one displaying the result?

W

Fairwinds's proposal can be qualified to accomodate...

Book3
	B	C	D	E
1		44
2		Vals
3		7
4		5
5		7
6		9
7		0
8
9
10		4
11		5
12		7
13
Sheet1

The formula in C1 is:

=SUM(INDEX(C:C,ROW()-ROW($C$1)+2):INDEX(C:C,MATCH(9.99999999999999E+307,C:C)))

Note that the formula ignores any text after row 1. Moreover, it doesn't check whether the relevant range contains any number, that is, it will give #N/A if the relevant range does not house at least one number, although a test can be added for such a test.