How to Use the COLUMNS Function in Excel

The COLUMNS function in Excel and Google Sheets is used to determine the number of columns within a specified array or range.

Syntax

=COLUMNS(array)

Explanation

The COLUMNS function requires just one parameter: array. This parameter may be either a range of cells or an array constant. The function calculates and returns the number of columns included in that array or range.

Examples

Here are some examples to illustrate how the COLUMNS function operates:

Example 1: Counting Columns in a Range

Imagine there is a cell range from A1 to D1, and you need to determine the number of columns in this range.

=COLUMNS(A1:D1)

The function will return 4 because there are four columns within the specified range.

Example 2: Counting Columns in an Array

You may also utilize the COLUMNS function with arrays. Consider the array below:

{10,20,30;40,50,60}

To determine the number of columns in this array, you would use:

=COLUMNS({10,20,30;40,50,60})

The result is 3, indicating there are three columns in this array.

Example 3: Integrating with Other Functions

The COLUMNS function can be extremely useful when integrated with other functions. For example, to create a dynamic formula that sums values in a row up to the nth column, you might use the COLUMNS function in the following way:

=SUM(A1:INDEX(1:1,1,COLUMNS(A1:D1)))

This formula calculates the sum of values from A1 to the nth column in the same row, where n is the total number of columns in the range A1:D1.

These examples highlight the practicality of the COLUMNS function in Excel and Google Sheets, providing essential functionality in various scenarios where dynamic column counting is required.

More information: https://support.microsoft.com/en-us/office/columns-function-4e8e7b4e-e603-43e8-b177-956088fa48ca

Other functions

ADDRESS

Returns a reference as text to a single cell in a worksheet

AREAS

Returns the number of areas in a reference

CHOOSE

Chooses a value from a list of values

COLUMN

Returns the column number of a reference

FILTER

Filters a range of data based on criteria you define

FORMULATEXT

Returns the formula at the given reference as text

GETPIVOTDATA

Returns data stored in a PivotTable report

HLOOKUP

Looks in the top row of an array and returns the value of the indicated cell

HYPERLINK

Creates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet

INDEX

Uses an index to choose a value from a reference or array

INDIRECT

Returns a reference indicated by a text value

LOOKUP

Looks up values in a vector or array

MATCH

Looks up values in a reference or array

OFFSET

Returns a reference offset from a given reference

ROW

Returns the row number of a reference

ROWS

Returns the number of rows in a reference

RTD

Retrieves real-time data from a program that supports COM automation

SORT

Sorts the contents of a range or array

SORTBY

Sorts the contents of a range or array based on the values in a corresponding range or array

TRANSPOSE

Returns the transpose of an array

UNIQUE

Returns a list of unique values in a list or range

VLOOKUP

Looks in the first column of an array and moves across the row to return the value of a cell

XLOOKUP

Searches a range or an array, and returns an item corresponding to the first match it finds If a match doesn't exist, then XLOOKUP can return the closest (approximate) match

XMATCH

Returns the relative position of an item in an array or range of cells