Cellwise Start free

Lookup function

Excel COLUMNS function

COLUMNS returns how many columns a range or array has, filled or not. Its best trick is inside VLOOKUP: COLUMNS($A$1:B1) counts 2, then 3, then 4 as you fill a formula to the right, so one formula can return several columns.

=COLUMNS(array)

Arguments

array
A range such as B1:G1, an array constant, or a formula that returns an array.

COLUMNS examples

Every result below was calculated from this sample sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEFG
1StoreJanFebMarAprMayJun
2Downtown184001725019900211002048022650
3Airport121001180013450129001420015020
4Mall156001498016200157501690017330

Download this sheet as an Excel practice file (.xlsx)

  1. =COLUMNS(B1:G1)

    Result: 6

    The table covers 6 months. Use it in a label such as "Average over 6 months" that updates when you add a month.

  2. =VLOOKUP("Airport",$A$2:$G$4,COLUMNS($A$1:B1),FALSE)

    Result: 12100

    COLUMNS($A$1:B1) is 2, so this returns Airport’s January sales, 12,100. Fill it right and it becomes 3, 4, 5..., returning Feb, Mar, Apr without editing the formula.

  3. =SUM(B2:G2)/COLUMNS(B2:G2)

    Result: 19963.333333

    Downtown’s monthly average, about 19,963. Unlike AVERAGE, an empty month would count as a zero here, which is what you want for a month with no sales.

  4. =COLUMNS(B2:G4)*ROWS(B2:G4)

    Result: 18

    How many cells the sales block has: 6 columns times 3 rows is 18.

How to use COLUMNS

  1. Click the cell for the result.
  2. Type =COLUMNS( and select the range.
  3. Close the bracket and press Enter.
  4. For a column number that grows as you fill right, lock only the start: COLUMNS($A$1:B1).

Common COLUMNS mistakes

Mixing up COLUMNS and COLUMN

COLUMN(D1) returns the column number, 4. COLUMNS(B1:D1) returns how many columns the range has, 3.

Hard-coding the VLOOKUP column number

A typed 2 does not change when you fill right, so every column returns January. COLUMNS($A$1:B1) counts up as it moves.

Expecting it to skip empty columns

COLUMNS counts every column in the range. Use COUNTA on a header row to count the columns that have a heading.

COLUMNS FAQ

How do I count columns in Excel?

Use =COLUMNS(A1:Z1) for a range. To count columns that have a header, use =COUNTA(1:1) on the header row.

How do I make the VLOOKUP column number change when I copy across?

Replace the number with COLUMNS($A$1:B1). Locked at the start and free at the end, it returns 2 in the first column and adds 1 for each column you fill right.

Does COLUMNS work in Google Sheets?

Yes, with the same syntax and result.

Guides that use COLUMNS

Related functions

Updated