Yesterday, in the 30XL30D challenge, we got cell details with the **CELL function**, and learned that it’s useful for a few things, like extracting a worksheet name.

For day 12 in the challenge, we’ll examine the COLUMNS function. Will this function be as useful? Or is it just another lazy function, like **AREAS**? Well, it does count the columns, as promised, and plays well with others, but nothing too exotic or powerful here.

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

So, let’s take a look at the COLUMNS information and examples, and if you have other tips or examples, please share them in the comments.

### Function 12: COLUMNS

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

### How Could You Use COLUMNS?

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

- 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

If you’re using a range reference, it must be a contiguous range.

### Example 1: Count Columns in an Excel Table

In Excel 2007 and Excel 2010, you can create a formatted Excel table, and refer to its name in a formula. In this example, there is a table named RegionSales,

and the COLUMNS function counts the number of columns in that table.

**=COLUMNS(RegionSales)**

### Example 2: Sum Last Column in Named Range

If you combine the COLUMNS function with SUM and INDEX, you can get the total for the last column in a named range. Here, the range is named MyRange,

and this formula sums the last column in the named range.

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

### Download the COLUMNS Function File

To see the formulas used in today’s examples, you can download the **COLUMNS function sample workbook**. The file is zipped, and is in Excel 2007 file format.

### Watch the COLUMNS Video

To see a demonstration of the examples in the COLUMNS function sample workbook, you can watch this short Excel video tutorial.

YouTube link: **Count Columns With Excel COLUMNS Function**

_____________