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.

 

[onlyadrafter]

Hello and welcome to the board.

It seems you just need a formula like

=IF(J3="","",SUM(J3:J65536))

is this the kind of thing you are after, or have I missed the point completely?

 

[fairwinds]

... maybe:

=SUM(A3:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

 

[Walky]

Thanks for the formula, it works.

Could you please explain the functions in it? What the Index and Match stand for?

=SUM(A3:INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

Rgds,

W

 

[fairwinds]

The 9.99999999999999E+307 is the biggest number excel can handle.
Using that in a Match formula like that returns the position of the last numerical entry in a column.

The Index function used like this returns a reference, thus combined with A3: gives you the range to sum.

 

[Walky]

Thanks fairwinds.

Onlyadrafter, I replied saying thanks for the welcome and mentioning that the formula didn't work if there was an empty cell before the first number, but it didn't post...

W.

 

[Walky]

Fairwinds,

There are 2 problems with your formula:

1: if there are no values in the column (to be entered when the info is available) it produces an error.

2: if there's only one value, it doesn't do the addition...

For the moment, I've reverted to the 1st option using hidden ZEROs on the 3rd row to make sure the formula works.

 

[fairwinds]

For your first problem, try this instead:

=IF(A3<>"",SUM(A3:INDEX(A:A,MATCH(9.99999999999999E+307,A:A))),0)

However I cannot understand what you mean by not adding if one value.

 

[earlyd]

Just observing this one, came up with a question...

If I pull the index piece out of the function, I get a value and not a reference. Why? Never saw this technique and decided to try it but a bit puzzled at the moment.

 

[fairwinds]

Just observing this one, came up with a question...

If I pull the index piece out of the function, I get a value and not a reference. Why? Never saw this technique and decided to try it but a bit puzzled at the moment.

Check it out in the help file:

"Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.

The INDEX function has two syntax forms: array and reference. The array form always returns a value or an array of values; the reference form always returns a reference."