## How to use the COLUMNS function

The COLUMNS function allows you to calculate the number of columns in a cell range.

#### Table of Contents

## 1. COLUMNS Function Syntax

COLUMNS(*array*)

## 2. COLUMNS Function Arguments

array |
Required. A cell range for which you want to determine the number of columns. |

## 3. COLUMNS Function example

The example above shows that cell range C2:J2 has 8 columns.

Formula in cell B6:

## 4. Count columns in an array

The COLUMNS function also calculates the number of rows in an array.

The array has four columns. An array uses delimiting values to separate columns and rows. They may differ between users based on their regional settings.

The formula above uses comma as a column delimiting character and semicolon as a row delimiting character.

83,89,63,81;

54,68,45,2

It is easier to see that the array actually contains four columns if I put values after a semicolon on a new row.

## 5. Count columns in a string

Formula in cell B6:

### 5.1 Explaining formula

#### Step 1 - Split string into an array

TEXTSPLIT(B3,",",";")

#### Step 2 - Count columns in the array

COLUMNS(TEXTSPLIT(B3,",",";"))

## 6. Count columns in multiple cell ranges

Formula in cell B12:

### 6.1 Explaining formula

#### Step 1 - Join cell ranges horizontally

HSTACK(C2:J2,C5:F5,C8:G8)

#### Step 2 - Count columns

COLUMNS(HSTACK(C2:J2,C5:F5,C8:G8))

## 7. Count columns in multiple Excel Tables

Formula in cell B8:

### 7.1 Explaining formula

#### Step 1 - Join Excel Tables horizontally

HSTACK(Table1, Table2)

#### Step 2 - Count columns

COLUMNS(HSTACK(Table1, Table2))

### 'COLUMNS' function examples

The following 34 articles contain the COLUMNS function.

This post explains how to lookup a value and return multiple values. No array formula required.

This article describes a formula that counts values in two columns if they are duplicates on the same row. What's […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

The drop down calendar in the image above uses a "calculation" sheet and a named range. You can copy the drop-down […]

This article demonstrates how to distribute values into specific ranges with possible overlapping ranges. I have written articles about filter […]

This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]

Lookup with criteria and return records.

Question: I have a list and I want to filter out all rows that have a value (Column C) that […]

Question: I second G's question: can this be done for more than 3? i.e. (Instead of last name, middle, first) […]

The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell […]

I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]

The following conditional formula highlights only the second instance or more of a value in a cell range. Conditional formatting […]

The following formula highlights cells that contain unique distinct values, in other words, all duplicate values except the first instance […]

(Chart data is made up) This article demonstrates two ways to color chart bars and chart columns based on their […]

This article demonstrates a formula and a VBA macro that returns every n-th row from a given cell range. The […]

This article describes a formula that returns all rows containing at least one digit 0 (zero) to 9. What's on […]

This article demonstrates a formula that points out row numbers of records that overlap the current record based on a […]

This article demonstrates several ways to check if a cell contains any value based on a list. The first example […]

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns […]

This article demonstrates a formula that extracts a row or record from a data table using two strings as criteria. […]

Question: Can expand this equation set into more than two colums of data, say if I had a first, middle […]

This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]

The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]

RU asks: Can you please suggest if i want to find out the rows with fixed value in "First Name" […]

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

I will in this article demonstrate how to search a table for a date based on a condition and then […]

Table of Contents Search values distributed horizontally and return the corresponding value Filter values distributed horizontally - Excel 365 1. […]

This article demonstrates how to sort a data set by multipe columns using the SORTBY function, Ecel Table and Excel […]

This article demonstrates a formula that sorts items arranged horizontally based on the adjacent numbers, every other column contains a […]

Question: How do I divide values equally into groups (3 lists or less)? This post shows you two different approaches, […]

I will in this article demonstrate how to use a value from a drop-down list and use it to do […]

Question: Does anyone know how to do a VLOOKUP of three columns to pull a single record? Table of Contents VLOOKUP […]

## Functions in this article

### Functions in 'Lookup and reference' category

The COLUMNS function function is one of many functions in the 'Lookup and reference' category.

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse html character entities instead of less than and larger than signs.

< becomes < and > becomes >

How to add VBA code to your comment[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

How to add a picture to your comment:Upload picture to postimage.org or imgur

Paste image link to your comment.

Contact OscarYou can contact me through this contact form