Description
Use this function to determine the number of columns in a specified array or reference.
Syntax
COLUMNS(array)
Inputs
This function accepts the following argument:
| Name | Required | Description | Valid Input |
|---|---|---|---|
array |
Yes | The range or array to use for calculating the number of columns. | An cell range, a formula that returns a cell range, or a reference to a cell containing a cell range. |
Examples
Sample formula
| Use case | Formula | Explanation and Result |
|---|---|---|
| Dynamically return a range of data from a large table or array. | =INDEX(A1:Z1, , COLUMNS(A1:B1)) |
This formula returns the value from the "n"-th column (where n is the number of columns in the range A1:B1), dynamically adjusting as you increase the range. |
| Calculate the average of values in a row but only for a certain number of columns, which will change dynamically based on the columns selected. | =AVERAGE(A1:INDEX(A1:Z1, COLUMNS(A1:B1))) |
This formula will averages the values from the first column up to the n-th column based on how many columns you select in the range A1:B1. |
| Create a running total formula that sums values across multiple columns, and the number of columns should be dynamic based on user input. | =SUM(A1:INDEX(A1:Z1, A2)) |
In this formula, the value in cell A2 tells you how many columns to sum. So, if A2 contains 3, the formula will sum the values in columns A1, B1, and C1. |
| Count the number of non-blank cells in a dynamic range where the number of columns may change. | =COUNTA(A1:INDEX(A1:Z1, COLUMNS(A1:B1))) |
This formula counts the number of non-blank cells in the first "n" columns based on the number of columns selected in A1:B1. For this data set, the result will be "0", as there are no empty cells. |
| In a lookup have the lookup range to adjust automatically as columns are added or removed. | =VLOOKUP(D1, A1:INDEX(A1:Z1, COLUMNS(A1:B1)), 2, FALSE) |
In this formula, the range A1:Z1 expands or contracts based on how many columns are selected in A1:B1, dynamically adjusting the lookup range. |
| Identify the last column in a row that contains data, and have it adjust dynamically as columns are added or removed. | =COLUMNS(A1:Z1) - MATCH(TRUE, ISBLANK(A1:Z1), 0) + 1 |
So, if the data starts at column A and ends at column F, this formula will return 6 (as column F is the 6th column). |
| Return the column number of the last non-empty cell in a row. | =COLUMNS(A1:Z1) - MATCH(FALSE, ISBLANK(A1:Z1), 0) + 1 |
This formula calculates how far from the start of the row the last non-blank cell is. |
Notes
- Column numbers start from 1, not 0. So "A" is column 1.
Tips
- COLUMNS can be combined with other functions, including:
- FILTER: To select specific columns from filtered data.
- SORT: To rearrange columns.
- UNIQUE: To extract unique values from selected columns.
- SEQUENCE: To create dynamic column selections.
- INDEX: For advanced column referencing.
- MATCH: For dynamic column selection based on criteria.
- TRANSPOSE: To reorient selected columns.
- CHOOSEROWS: For more complex data selection.
Related functions
Here are the other array formula functions supported in Workiva spreadsheets.