Quick Counting in Excel

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

<span class="text">largest row - smallest row + 1</span>

. 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

<span class="text">Sum=938</span>

. 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:

=VLOOKUP(5,

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.

=VLOOKUP(5,A2:M21,9,FALSE)

is the resulting formula that returns the Telephone column.

9 Comments

  1. Rick Williams says:

    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.

  2. geoff says:

    Me three. Although if there’s enough columns I’ll use MATCH on the header row.

  3. Jon von der Heyden says:

    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.

  4. chet says:

    I hope those SSN’s aren’t real.

  5. Rob van Gelder says:

    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!

  6. 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/

  7. JP says:

    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.

  8. Ross says:

    Good tip, I cant belive I’ve been counting columns like a fool for so long! thanks Dick
    Ross

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: