COLUMNS Function Examples

These examples show how I use the Excel COLUMNS function, which returns the count of columns in an array or reference.

Video: COLUMNS Function

The COLUMNS function returns the count of columns in an array or reference.

To see how the COLUMNS function works, watch this short video. It shows some of the examples from this tutorial.

Get the Sample File

To see the formulas used in these examples, get the COLUMNS function sample workbook. The file is zipped, and is in Excel xlsx file format. There are no macros in the workbook

Uses for COLUMNS

The COLUMNS function can show the size of a table or named range. For example:

• Count columns in an Excel Table
• Sum last column in a named range
• Count all columns on worksheet

Tip: Use the Excel ROWS function (Microsoft help page) to return the number of rows in an array or range.

COLUMNS Syntax

The COLUMNS function has the following syntax:

• COLUMNS(array)
  • array is an array or array formula, or reference to a range.

COLUMNS Traps

There is something to keep in mind when using COLUMNS:

• If you're using a range reference, it must be a contiguous range

Ex 1: Count Columns in Table

In this example, there is a named Excel table -- RegionSales.

When a cell in the table is selected, you can see the table name on the Table Design tab, at the top of the Excel window.

With the COLUMNS function, you can refer to that table name, and count the number of columns in the RegionSales table.

Here is the formula in cell C2:

• =COLUMNS(RegionSales)

There are 4 columns in the table, and that is the result shown in cell C2.

Ex 2: Sum Last Column in Named Range

In this example, the Microsoft Excel COLUMNS function is in a formula with SUM and INDEX. Those functions work together, and return the total amount in the last column of a named range of cells.

Here is the named range, MyRange, in cells B4:E8.

In cell C2, this formula sums the numbers in the last column of the named range, MyRange:

• =SUM(INDEX(MyRange,,COLUMNS(MyRange)))

How It Works

Here's how that formula works:

1. The INDEX function returns all of the values from the last column in MyRange
• Its 3 arguments are array, row_num and column_num
  • array: MyRange is the array
  • row_num: Row number is omitted, so all the rows are returned
  • column_num: COLUMNS returns the number of the last column in MyRange
2. The SUM function totals all the values that INDEX returns

See more INDEX function tips and examples on the INDEX and MATCH page.

Example 3: Count Columns on Worksheet

In this example, the COLUMNS function counts the number of columns across the entire worksheet.

To get that count, the formula will refer to an entire row -- 1:1

• Note: It doesn't matter if any of the cells in that row have values, or not. The COLUMNS function counts the columns, not the contents of the cells.

Here is the column in cell C3:

• =COLUMNS(1:1)

There are 16384 columns in row 1 on this worksheet, and that is the result shown in cell C3.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

Get the Sample File

To see the formulas used in these examples, get the COLUMNS function sample workbook. The file is zipped, and is in Excel xlsx file format. There are no macros in the workbook

 

More Tutorials

SUM Function

INDEX and MATCH Functions