How can I create a SUM formula for an unknown amount of numbers in a column?

I'm creating a template for a coworker, and I need to sum a column in a given sheet. The fields are usually entered by pasting the information from a different location, and these numbers are never the same every month. The easy solution would be to put the sum above the data, but I'm trying to do things right.

From all the research I've done so far, the cleanest solution seems to be to add a scroll bar, but that doesn't seem like it's intended for what I'm doing.

Oh, and this template will have four sheets with the same layout (one for each week a month), if that makes any difference.

For reference, up to now, she (and all other account executives) are doing things the hard way. Most things are hard coded and written out manually each week/month.


Thanks in advance for any help.

This formula will sum the "A" column, then divide by the number of cells that contain numbers in that column. It ignores any cell that is text. To change the column that it culculates, just change all of the "A"s to the proper letter.

=(Sum(A:A)/Count(A:A))

You can restrict how far down the spreadsheet the formular calculates as well. Just add the numbers of the cells next to the letters.

(i.e. =(Sum(A3:A500)/Count(A3:A500)))
This adds up all values between A3 and A500. Than it divides by the number of cells it found between A3 and A500 that have numbers in them.

@Cheeky Charlie: I think that I will just put it on top after all. Thanks for the help though, I guess it's not such a bad thing.

@tplunkett41: I tried it this way but it didn't work out. I will just give in and put the formula above and use a simple sum for an entire column.


Thanks for the replies.

Have you considered using a 'Table' rather than a range? You can do many different calcs within a table. If you can attach a sample of your data - I'll give it a go...

Originally Posted by Steve N.

Have you considered using a 'Table' rather than a range? You can do many different calcs within a table. If you can attach a sample of your data - I'll give it a go...

Steve,

The only question I have is can I make it so that pasted information is automatically turned into a table? I already have the headers for each column, so I just need it to calculate for data that's not there yet.

I'm basically trying to make it so there's as little user interaction as possible, since it's a template.