Counting or totalling cells in a column

[IanWilson]

Sorry for what is probably a very basic question, but my Excel knowledge is very basic!

I have a spreadsheet of exam questions, with some rows at the top that are column headings etc. One of these I want to be the total number of questions in that column, or the total number of marks in that column as the case may be.

At the moment I am using a formula like =COUNTA(E4:E110) to count the number of questions in the column (i.e. the number of cells with an entry in rows other than the first three rows) or =SUM(F4:F110) to total the marks in the column. 110 is just a number that is larger than I think the number of rows is likely to be, but if I ended up adding many more rows, I'd need to change the formula to say =COUNTA(E4:E200) or something like that.

Is there a different formula I can use that just counts, or totals, all the cells in the column apart from the first three rows, without having to worry about whether the range of cells I have specified goes far enough down the sheet?

Ian

[HansV]

Let's say that E1 and E3 contain text and E2 is blank. You could use =COUNTA(E:E)-2 to count the number of non-blank cells in column E from E4 down. Adjust as needed.
The SUM function ignores text values, so if F1:F3 contain text, you could use =SUM(F:F) to add the marks.

[IanWilson]

Let's say that E1 and E3 contain text and E2 is blank. You could use =COUNTA(E:E)-2 to count the number of non-blank cells in column E from E4 down. Adjust as needed.
The SUM function ignores text values, so if F1:F3 contain text, you could use =SUM(F:F) to add the marks.

Thanks.

Or if there were a danger that there might be non-text values I wanted to ignore, I suppose I could use something like

SUM(F:F) - SUM(F1:F3)

could I?

Ian

[HansV]

Yes, that would work too.

[IanWilson]

Let's say that E1 and E3 contain text and E2 is blank. You could use =COUNTA(E:E)-2 to count the number of non-blank cells in column E from E4 down. Adjust as needed.
The SUM function ignores text values, so if F1:F3 contain text, you could use =SUM(F:F) to add the marks.

Sorry - another thing. I should have said that the cell containing the formula is in the same column, so for instance in E3 I want to count the number of entries from E4 downwards. But following your advice, I'm finding that if I use =COUNTA(E:E) then Excel warns me that I am using a circular reference.

Ian

[HansV]

You could use =COUNTA(E4:E65536) in Excel 97-2003, or =COUNTA(E4:E1048576) in Excel 2007 or later, since the number of rows in a sheet is 65536 and 1048576 in those versions.

[IanWilson]

You could use =COUNTA(E4:E65536) in Excel 97-2003, or =COUNTA(E4:E1048576) in Excel 2007 or later, since the number of rows in a sheet is 65536 and 1048576 in those versions.

Thanks. If I make it count so many rows, does that affect the file size?

Ian

[HansV]

No, Excel is pretty efficient when it comes to handling blank cells.