Excel COLUMNS function examples show how to make the best use of this function, which returns the count of columns in an array or reference.

Author: Debra Dalgleish

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.

- Count columns in an Excel Table
- Sum last column in a named range

The COLUMNS function has the following syntax:

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

There is something to keep in mind when using COLUMNS:

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

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.

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)))**

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

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

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.

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

Last updated: April 16, 2024 3:58 PM