Excel COLUMNS function
- Example1: Count total number of columns in a range
- Example2: Count total number of cells in a range
- Example3: Obtain the last cell address of a given range
Description
The COLUMNS function returns the total number of columns in a given array or reference. For example, COLUMNS(A1:D4) returns 4.
Syntax and arguments
Formula syntax
| =COLUMNS(array) |
Arguments
|
Return Value
The COLUMNS function returns the number of columns in an array or reference.
Usage and Examples
With the COLUMNS function you not only can count the numbers of a range, but also solve other jobs.
Example1: Count total number of columns in a range
Formula1
=COLUMNS(A1:D1)
Explain: Count the total number of columns in the range of A1:D1
Result:4
Formula2
=COLUMNS(DATA)
Explain: Count the total number of columns in the defined name range of “DATA” (range A1:D10)
Result: 4
Example2: Count total number of cells in a range
With combining the ROWS function and the COLUMNS function, you can obtain the total number of cells in a given range.
Formula
=ROWS(DATA)*COLUMNS(DATA)
Explain:
Data: the defined name which you want to count the total number of cells.
ROWS: return the total number of rows from the given reference
Result: 40
Example3: Obtain the last cell address of a given range
Formula
=ADDRESS(ROWS(DATA),COLUMNS(DATA))
Explain:
ADDRESS(row_num,column_num): returns the cell address based on the given row number and column number.
ROWS(): returns the total number of rows.
COLUMNS(): returns the total number of columns.
Then the formula shown as this:
=ADDRESS(10,4)
Result: $D$10
Sample File
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.