Saturday, February 24, 2007

An Excel Range Tip

This formula does not look correct =COUNTA(A:A), but it is. By entering a range with column letters without row numbers, Excel assumes you are referring to all the active cells in the column listed. And it works the same for rows. =SUM(15:15) will add up all cells in row 15.
=SUM(A:B) would add up all the cells in both column A and B.

Cute, but is it useful? Yes, in a worksheet of email addresses I use for one of my emailings, I need to know how many are on the list. At the top of the worksheet I created a formula containing =COUNTA(A:A)-1 to count the number of emails in the list. I subtracted 1 to exclude counting the row containing the column heading.