There are several ways to count cells in Excel. Here are a few.
1. Subtract rows
This is how our grandparents did it. If your data is in one column, you can compute in your head largest row - smallest row + 1. Watch out for those off by one errors.
2. Use QuickSum
On the right side of status bar (runs along the bottom of the Excel application), you might see Sum=938. If you right click, you can change to Count. This has the advantage of working with rows, columns, or any size block. It also ignores blank cells.
3. Use the Name Box
The Name Box is the area to the left of the formula bar. Normally it shows the address of the selection. This is my favorite because it can be all keyboard and it works with rows and columns. If I have a big range of random data, I can select it and before I release the mouse button (or the control+shift key combination) the name box will show me the rows and columns.
If there are blanks in the data, this obviously doesn’t work. This is also handy when constructing a VLOOKUP formula. The third argument of VLOOKUP is the column you want to retrieve. In the above example, let’s assume I want to retrieve the telephone number. I type:
then start selecting my lookup range. While your in formula entry mode, the name box won’t help, but the same information is in a tooltip following your cursor.
Note that I pause when I get to the Telephone column and the tooltip says “1R x 9C”. I can continue to select the range knowing that 9 will be my third argument.
is the resulting formula that returns the Telephone column.
It’s funny how similarly people work sometimes, despite having developed their own techniques themselves. Call it parallel invention. ;)
I use the same method when writing VLOOKUPs too.
Me three. Although if there’s enough columns I’ll use MATCH on the header row.
If I always want to return the last column then I tend to use:
=VLOOKUP(5,A2:M21,COLUMNS(A:M),0)
I think it’s a little safer because if a user inserts a column between A:M then using the columns function will automatically reference the 10th column. Using a constant can be risky business and yield incorrect results.
I hope those SSN’s aren’t real.
If you select more than a page of cells, the dimensions move from the name box to a tooltip under the mouse cursor.
While I don’t mind the dimensions appearing against the mouse cursor, I wish they’d leave it in the name box instead of making it blank!
They’re probably real for somebody, but if they match up with those names that’d be one heck of a coincidence. They came from JP’s random data generator. http://www.codeforexcelandoutlook.com/blog/2009/02/random-sample-data-generator-add-in-for-excel-now-available/
Thanks Dick for another mention of the add-in. The SSNs are generated using the Rnd() function as follows:
= (Int((999 – 100 + 1) * Rnd + 100)) & “-” & (Int((99 – 10 + 1) * Rnd + 10)) & “-” & (Int((9999 – 1000 + 1) * Rnd + 1000))
No actual SSNs or other data was used in the creation of this add-in. Any resemblance to actual SSNs or living people is coincidence. Except for Ms. Farmer of Bristol, MS, please accept my humblest apologies.
Good tip, I cant belive I’ve been counting columns like a fool for so long! thanks Dick
Ross
[…] Alexander Dick Kusleika Bob […]