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.

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

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

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:

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

The SUM function totals all the values that INDEX returns

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